topic Re: YTD, MTD, QTD dax for fiscal year in DAX Commands and Tips

YTD, MTD, QTD dax for fiscal year

YogeshWaran2010 — Wed, 08 Nov 2023 07:54:52 GMT

Hi,

We have a slicer which will change between YTD, MTD, QTD using selectedvalue dax we will populate the table. Our fiscal year starts on March

Currently, we are using these dax for YTD and QTD,
i) totalYTD = CALCULATE([total_amount], DATESYTD('dim_date (2)'[date_column], "28/2"))
ii) totalQTD = CALCULATE( [total_amount],
FILTER(

ALL('dim_date (2)'[date_column]),

'dim_date (2)'[date_column] >= STARTOFQUARTER(DATEADD('dim_date (2)'[date_column], 0, MONTH))

&& 'dim_date (2)'[date_column] <= MAX('dim_date (2)'[date_column])

)
iii) totalMTD = SUM(amount) since the granuality of data is at a level of months
We are facing issues in YTD since febraury might be a leap year we are unable to give correct Year end date.
In QTD we are facing issue in which the QTD sums quarter based on calendar quarter.
Thank you
Yogesh

Re: YTD, MTD, QTD dax for fiscal year

ThxAlot — Wed, 08 Nov 2023 09:10:20 GMT

My unique way to tackle any fiscal year questions, no matter a fiscal year ends in Feb or any month of the year.

DATES = ADDCOLUMNS( CALENDAR( EOMONTH( MIN( SALES[Date] ), -1 ) + 1, EOMONTH( MAX( SALES[Date] ), 0 ) ), "FY Date", EDATE( [Date], -2 ), "FY Yr-Mth", FORMAT( EDATE( [Date], -2 ), "yyyy-MM" ) )

Re: YTD, MTD, QTD dax for fiscal year

YogeshWaran2010 — Wed, 08 Nov 2023 10:32:48 GMT

Hi ThxAlot ,
Thank you for your fast replay. I have tried out the calendar table that you used but I am still getting the rolling total till Dec and not for Jan and feb. I have attached the sample pbi file

PBI File - click here

Re: YTD, MTD, QTD dax for fiscal year

ThxAlot — Wed, 08 Nov 2023 12:47:11 GMT

Use "FY Date" or "FY Yr-Mth" in viz.