Forum Discussion
Solved
Period Filter/Slicer use the value selected in different ways for different visulaisations
Hi I have 3 tables in my report that are showing MTD and YTD Measures. In order to show these correctly I need to set the Filter value for Period to "is" equal to the period. For example is 8. ...
When you say period it is not clear you taking of custom period. All time intelligence functions work best with date calendar.
Can you explain your need with better example
Few examples
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr)))) MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date]))) MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31"))) QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER)) trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) 55 weeks behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],365+12,Day)) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]))) Cumm Sales year behind= CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year)))) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date]))) Cumm Days = CALCULATE(distinctcount(date[date]),filter(date,date[date] <=max(Sales[Sales Date]))) Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH)) Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day)) Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day)) Rolling 4 week same week day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-28,Day),filter(Date, WeekDay('Date'[Date])=max( WeekDay('Date'[Date])))) 3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH))) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31")) Rolling last 60 days = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-60,Day)) Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day)) Today = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=Today()) Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH)) Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH)) Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH)) Rolling 11 till after 6 month = CALCULATE(sum('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('Date'[Date],startOFMONTH(dateadd(Sales[Sales Date],6,month)),11,MONTH)) Rolling 3 = CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0 Rolling 3 = CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0 Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-7,7,day)) Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-14,7,day)) -- Force today filter YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today()) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))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/
amitchandak
Super User
Super UserWhen you say period it is not clear you taking of custom period. All time intelligence functions work best with date calendar.
Can you explain your need with better example
Few examples
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER)
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
55 weeks behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],365+12,Day))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales year behind= CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
Cumm Days = CALCULATE(distinctcount(date[date]),filter(date,date[date] <=max(Sales[Sales Date])))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day))
Rolling 4 week same week day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-28,Day),filter(Date, WeekDay('Date'[Date])=max( WeekDay('Date'[Date]))))
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))
Rolling last 60 days = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-60,Day))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Today = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=Today())
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))
Rolling 11 till after 6 month = CALCULATE(sum('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('Date'[Date],startOFMONTH(dateadd(Sales[Sales Date],6,month)),11,MONTH))
Rolling 3 = CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0
Rolling 3 = CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0
Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-7,7,day))
Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-14,7,day))
-- Force today filter
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))
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/
I have a Date Table that aligns each date to an accounting Period or a month.
My Table shows the MTD Spend and YTD Spend. When the user selects February, which is our accounting period 8, my time intelligence functions work as expected. MTD returns the Month Spend for February, and the YTD returns the Sum of July-Feb. However, the line chart does not and only shows plot for February, not the months of July - February.
If I select all months from July to February in my filter, the Line chart shows correctly however, my MTD numbers show the YTD numbers to February, not the MTD for February.
I want the user to select the Month i.e. February, or alternatively select all the months from July - Feb and the table show the correct MTD and YTD numbers and the Line Chart show all months from Jul - Feb.
I hope this is clearer?
Thanks,
- amitchandak
timridolfi , Totalytd and datesytd can take year-end date. In the example, I shared I used 12/31. You use 6/30. That should work for YTD
amitchandak Thank you for your responses. I have found if I use the DATESMTD and DATESYTD within CALCULATES, as opposed to the TotalMTD and TotalYTD functions, gives me what I need. I did not realise they worked differently.
- amitchandak
TotalYTD also takes year-end date. The year-End date does mean it start with the end date. It just we are telling the correct financial year.