Solved: Re: YTD MTD Calculations which need to show result... - Page 2

Anonymous · ‎09-14-2019

Hi All,

I have created the YTD and MTD calculations with my data which contains a table with all the required columns(including date column)
My formula is as follow:

MTD=CALCULATE([Amount],DATESMTD('Table'[Date]))

MTD LY=CALCULATE([MTD], DATEADD('Table'[Date].[Date],-12,MONTH))

YTD=CALCULATE([Amount],DATESYTD('Table'[Date]))

YTD LY=CALCULATE([YTD], DATEADD('Table'[Date].[Date],-12,MONTH))

Now I add two filters in my report- Year filter and Month filter.

The code is working fine with these filter, but my client doesn't want these two filter instead he want one filter(Month_Year).
I have created the Month_Year filter as well

Month_Year = FORMAT('Table'[Date],"MMM") & "-" & YEAR('Table'[Date])

But as this will be in text format, so when I select a value from it, my YTD/MTD measure doesn't show the correct value.

How I can rectify this and get correct values while filtering the data with Month_Year filter?

Thanks
Shubs

amitchandak · ‎09-16-2019

Check

 Last Year YTD Sales = 
Var _max_end_date = CALCULATE(MAXX(Dates,DATEADD(DATEADD(Dates[Date],-12,MONTH),1,DAY)),ALLSELECTED(Dates[Date]))
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFYEAR(DATEADD(Dates[Date],-12,MONTH))),ALLSELECTED(Dates[Date]))
Return
 CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date,CROSSFILTER(Data[Date],Dates[Date],None))
 
 YTD Sales = 
Var _max_end_date = CALCULATE(MAXX(Dates,DATEADD(Dates[Date],1,DAY)),ALLSELECTED(Dates[Date]))
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFYEAR(Dates[Date])),ALLSELECTED(Dates[Date]))
Return
 CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date,CROSSFILTER(Data[Date],Dates[Date],None))

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

View solution in original post