Ciao, I'm curious about the CALCULATETABLE when the whole is already inside CALCULATE, I've had a look in ' THE BOOK', and can't find an explanation, if it's very complex please just say so. Richard.
CALCULATE returns scalar values (a single number or string) whereas CALCULATETABLE computes a table expression (one or more columns, one or more rows).
Hi. Great. How can we change the ytd start and end date. As default it takes calendar quarters. We need to change it for fiscal calendar like 1st feb as start.
Hello, I want to create a comparison of current year to date against previous years. I've used DATEADD and it works. -However, I need one of the previous years to be calendar year 2019. -Using minus 3 worked for year 2022. Now that it is year 2023 I will have to update the formula to minus 4. How can I filter for year 2019 without having to update my formula annually? See formula below: Legend: Pax = passengers (I'm analyzing commercial aviation data) '_Calendar'[Date] = date table Pax YTD 2019 = IF ( ISBLANK ( [Pax] ), BLANK (), TOTALYTD ( [Pax], DATEADD ( '_Calendar'[Date], -3, YEAR ) ) ) Thx in advance for you help.
When you use Date[Date], that is the internal conversion - the parameter is a table and it should be a list of dates. When you just provide a column reference, the remaining syntax is written for you automatically.
2:33 - 2:37 Just those 4 seconds and suddenly so much of CALCULATE suddenly made sense. Thanks!!!
Great sir. Hope to learn more about DAX measures and Practical examples.
Muy buena explicación muchas gracias me ayudó en mi reporte
Ciao,
I'm curious about the CALCULATETABLE when the whole is already inside CALCULATE,
I've had a look in ' THE BOOK', and can't find an explanation, if it's very complex please just say so. Richard.
CALCULATE returns scalar values (a single number or string) whereas CALCULATETABLE computes a table expression (one or more columns, one or more rows).
@@SQLBI So even though DISTINCT itself returns a table , using CALCULATETABLE is 'best practice' .
Very well explained.
Finished watching
Hi. Great. How can we change the ytd start and end date. As default it takes calendar quarters. We need to change it for fiscal calendar like 1st feb as start.
There is an additional parameter for the last day of the year, check the syntax on dax.guide
how amazon are these guys
Hello, I want to create a comparison of current year to date against previous years.
I've used DATEADD and it works.
-However, I need one of the previous years to be calendar year 2019.
-Using minus 3 worked for year 2022. Now that it is year 2023 I will have to update the formula to minus 4.
How can I filter for year 2019 without having to update my formula annually?
See formula below:
Legend:
Pax = passengers (I'm analyzing commercial aviation data)
'_Calendar'[Date] = date table
Pax YTD 2019 =
IF (
ISBLANK ( [Pax] ),
BLANK (),
TOTALYTD ( [Pax],
DATEADD ( '_Calendar'[Date],
-3, YEAR ) )
)
Thx in advance for you help.
Why date[date] is translated into CALCULATETABLE ( DISTINCT ( ) ? I can't find an explanation in the book or on SQLBI? Could someone explain it?
When you use Date[Date], that is the internal conversion - the parameter is a table and it should be a list of dates. When you just provide a column reference, the remaining syntax is written for you automatically.