topic Re: Obtaining last month graphic while using a date slicer in DAX Commands and Tips

Obtaining last month graphic while using a date slicer

ruesaint_denis — Tue, 28 Jun 2022 22:31:21 GMT

Hi everyone,

I would like to show the selected month and previous month data, allowing for the user to select a month via a Month Year slicer.

I have a User table, and a Date table. My Date table has month offset.

I'm not sure what I need to do to my measures, for it to still be able to take into consideration the selected month, but show me the values of the selected month -1. When I try to do that in my measure, shows up blank (the 3rd bar from the top on the "Previous Month" funnel).

I can't tell it to ignore the Month Year filter, because I need it in the calculation, to subtract by month offset -1.

Any thoughts?

This is an example of how I tried to filter for the previous month within my measure.

# Accredited Users (time based -1 month) = CALCULATE(DISTINCTCOUNT(Users[User Id]), FILTER(Users, Users[Accredited Status]="ACCREDITED" ), FILTER(ALLSELECTED('Date Table'), 'Date Table'[Month Offset]=-1), USERELATIONSHIP(Users[Date],'Date Table'[Date]))

But since I have the Date slicer on, it's not showing any data outside of the date slicer range.

Re: Obtaining last month graphic while using a date slicer

amitchandak — Wed, 29 Jun 2022 03:58:13 GMT

ruesaint_denis , You can use time intellignece with date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

For offset you need month rank (either on month start date or YYYYMM)

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Re: Obtaining last month graphic while using a date slicer

v-yalanwu-msft — Fri, 01 Jul 2022 05:33:50 GMT

Hi, ruesaint_denis ;

# Accredited Users (time based -1 month) = CALCULATE ( DISTINCTCOUNT ( Users[User Id] ), FILTER ( Users, Users[Accredited Status] = "ACCREDITED" ), FILTER ( ALL ( 'Date Table' ), EOMONTH ( [Date], 0 ) = EOMONTH ( MAX ( Users[Date] ), -1 ) ), USERELATIONSHIP ( Users[Date], 'Date Table'[Date] ) )

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Obtaining last month graphic while using a date slicer

ruesaint_denis — Tue, 05 Jul 2022 22:08:29 GMT

Hello amitchandak , Hello v-yalanwu-msft ,

Thank you, I tried both recommendations and ended up debugging to the following:

CALCULATE([# Accredited Users (time based)],DATEADD('Date Table'[Date],-1,MONTH))

Re: Obtaining last month graphic while using a date slicer

v-yalanwu-msft — Wed, 06 Jul 2022 01:13:25 GMT

Hi, ruesaint_denis ,

Has the result of the final debugging solved your problem? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.