Forum Discussion
Return Dynamic YTD Value Based on Slicer
Seems like this should be easy but can't quite get it. Want to get a dynamic MTD/YTD amount based on the slicer value I choose.
For example, using the data below, and the month Feb-20 is chosen, want the matrix to return a MTD amount of 45 and YTD amount 0f 70. And if Mar-20 is chosen the matrix should return a MTD amount of 65 and a YTD amount of 130.
Can't quite get my measure to work
Hope that makes sense. Thank you in advance for the help!
Hi asebes ,
As far as possible, create a relationship between the data table applied the slicer and the DATA table. If it is not possible, you can try the following formula to create measures.
MTD = var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo]) return CALCULATE( SUM(DATA[Amount]), FILTER( DATA, DATA[Period].[MonthNo] = selectvalue ) )YTD = var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo]) var t = CALCULATE( SUM(DATA[Amount]), FILTER( ALL(DATA), DATA[Period].[MonthNo] <= selectvalue ) ) return IF( MINX(DATA,DATA[Period].[MonthNo]) = selectvalue, t, BLANK() )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
asebes , You have default year for this case and use time intelligence with date table
Create a column like this in your date table and filter on this https://www.youtube.com/watch?v=hfn05preQYA
Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)TI Measures
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKATo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thank you for input! I'm still missing something though. So if i have a slicer that details all the months of the year, but then want the YTD formula to be based on the slicer month i choose. So if i chose March, the MTD column would be be March info, but the YTD column would give the sum of Jan, Feb & Mar.
Hi asebes ,
As far as possible, create a relationship between the data table applied the slicer and the DATA table. If it is not possible, you can try the following formula to create measures.
MTD = var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo]) return CALCULATE( SUM(DATA[Amount]), FILTER( DATA, DATA[Period].[MonthNo] = selectvalue ) )YTD = var selectvalue = SELECTEDVALUE('Calendar'[Date].[MonthNo]) var t = CALCULATE( SUM(DATA[Amount]), FILTER( ALL(DATA), DATA[Period].[MonthNo] <= selectvalue ) ) return IF( MINX(DATA,DATA[Period].[MonthNo]) = selectvalue, t, BLANK() )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for input! I'm still missing something though. So if i have a slicer that details all the months of the year, but then want the YTD formula to be based on the slicer month i choose. So if i chose March, the MTD column would be be March info, but the YTD column would give the sum of Jan, Feb & Mar.
