topic Re: Need help with Dax to display Previous Month Name in a Card based no a Slicer selection in DAX Commands and Tips

Need help with Dax to display Previous Month Name in a Card based no a Slicer selection

Anonymous — Thu, 03 Dec 2020 12:23:19 GMT

I have Created a DAX to display the Slicer Filtered Value in Card, but I am not able to write the same DAX to display Previous Month Name, Previous Quarter Name, or Previous Year Name based on the slicer selection.
For Eg: If Slicer is filtered for Feb, the Previous Month's Card should show Jan, similarly for Quarter and Year.

Dax for Current Selection:
Selected date Dynamic Title =

var curr_yr = YEAR(TODAY())

return

IF(ISFILTERED('Calendar Table'[Date].[Month]),

CONCATENATEX(VALUES ('Calendar Table'[Date].[Month]),'Calendar Table'[Date].[Month],",") & " " & VALUES('Calendar Table'[Date].[Year]),

IF(ISFILTERED('Calendar Table'[Date].[Quarter]),

CONCATENATEX(VALUES ('Calendar Table'[Date].[Quarter]),'Calendar Table'[Date].[Quarter],",") & " " & VALUES('Calendar Table'[Date].[Year]),

IF(ISFILTERED('Calendar Table'[Date].[Year]),

CONCATENATEX(VALUES ('Calendar Table'[Date].[Year]),'Calendar Table'[Date].[Year],","),

IF(ISFILTERED('Calendar Table'[Date]),

CONCATENATEX(VALUES ('Calendar Table'[Date]),'Calendar Table'[Date],","),

"Current Year"))))
The Goal here is to display 2 cards, one with current selection(I think I have achieved that) and the other one with previous names (Month, Quarter and Year) based on the selection on the slicer.

Thanks

Re: Need help with Dax to display Previous Month Name in a Card based no a Slicer selection

amitchandak — Thu, 03 Dec 2020 12:40:00 GMT

Anonymous , Make sure name you want to display is avaiable as columns in date table

Month Name = CALCULATE(max('Date'[Month Year]),DATESMTD('Date'[Date]))
last Month Name = CALCULATE(max('Date'[Month Year]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Last Qtr Name = CALCULATE(max('Date'[Qtr Year]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last year Name = CALCULATE(max('Date'[Year]),DATESYTD(dateadd('Date'[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 :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Need help with Dax to display Previous Month Name in a Card based no a Slicer selection

Anonymous — Thu, 03 Dec 2020 13:08:56 GMT

Thanks, amitchandak, this works perfectly. Really appreciate your help.

Thanks

Sathwik