How to make WTD MTD and YTD dynamic using slicer?

[Anonymous]

Hi,

I want to display the below details in a single table based on the date slicer I select.

For example, If I select week 3, then the table should display WTD of Incident date, Incident ID and Incident Time, similiarly, If I select only Month 5 then the table should automatically display MTD values of Incident date, Incident ID and Incident Time and so on..

Select either day, week, month, year - selecting day will show yesterday, selecting week will show WTD incidents, selecting month will show MTD incidents etc

I am not sure how to write DAX for this. I am thinking may be this could be soved using SWITCH. 

I am using the below dax to calculate WTD and MTD based on the selected date

Incidents MTD =

CALCULATE(

COUNT('SMS Incident'[Incident ID]),

FILTER(

ALL('ref Calendar'),

'REF Calendar'[Fiscal Current Month] = MAX('REF Calendar'[Fiscal Current Month])

&& 'REF Calendar'[Date] <= MAX('REF Calendar'[Date])

)

)

Incidents WTD =

CALCULATE(

COUNT('SMS Incident'[Incident ID]),

FILTER(

ALL('ref Calendar'),

'REF Calendar'[Fiscal Current Week Modified] = MAX('REF Calendar'[Fiscal Current Week Modified])

&& 'REF Calendar'[Date] <= MAX('REF Calendar'[Date])

)

)

Thank you. 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Keep your original slicer and create a table with the measure name as the slicer.

Refer to this blog:https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Foe WTD I have a blog

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

This is for direct query but will work import mode too

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

TD 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))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,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")))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))





QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(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)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(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))

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/

[Anonymous]

@amitchandak  Thank you for helping me out. Amit, I tried to understand what you suggested, but I could not understand the logic. I am sorry I am not a DAX expert. 

If you don't mind, I have attched the sample PBI. Could you please spare few minutes and create 3 measures for me? To make things clear, I have three slicers on the top for week, month and year. When I select week for example 3, the table below should display the WTD of incident ID's . Similary, when I select month 2 the table only display MTD of incident ID's. 

 Sorry to bother yo and thanks for helping me out. 

Please find the attched PBI Here 

[Anonymous]

Hi there.

Do this and you'll be OK. But this measure is tied to Week, Month, and Fiscal Year only. If you start using it for anything else.... well, you should not.

[Measure] =
var __filterLocation =
	HASONEFILTER( Calendar[Week] ) * 1
	+ HASONEFILTER( Calendar[Month] ) * 2
	+ HASONEFILTER( Calendar[Fiscal Year] ) * 4
var __result =
	switch( __filterLocation,
		1, [WTD],
		2, [MTD]
		4, [YTD]
	)
return
	__result

where, of course, [WTD], [MTD] and [YTD] are the corresponding measures.

Best

D