Forum Discussion
Table based on YTD/MTD
Hi
I want to show table based on YTD/MTD filter. When choosing MTD, only show MTD month(at this situation would be 2020.04), when choosing YTD, show values for 2020.01~2020.04 at this situation.
I wrote calculation like this, I cannot put this to the FILTERS pane to choose true/false...
Thanks
Yun
14 Replies
YunJ , In case you have Date use Time Intelligence and date calendar.
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH))) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))To 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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/You can create a date like.(Based on the format I can see)
Date = Date(Left([Month year],4),right([Month year],2),1)
Thanks for your reply. But I have many measures in the table. So I don't want to set MTD/YTD for per measure in its calculation... I prefer to set a month calculation like following to make a general calculation only for month like following.
YunJ Can you share sample data and sample output.
Hello amitchandak
For this pic, I'm wondering is my date slicer can be look like this right? rather than a range data(e.g. 202001~202004) or multiple choices(e.g. 202001,202002) right?
YunJ , As along as Year and Month coming from a date table, You can use time intelligence. Even if select year, month or date etc . The last date is your base.
