Obtaining last month graphic while using a date slicer

[ruesaint_denis]

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. 

[v-yalanwu-msft]

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.

[ruesaint_denis]

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))

[v-yalanwu-msft]

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.

[amitchandak]

@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))