Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.


Obtaining last month graphic while using a date slicer

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. 



Community Support
Community Support

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.

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

Hi, @ruesaint_denis ;

# Accredited Users (time based -1 month) =
    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.


Super User
Super User

@ruesaint_denis , You can use time intellignece with date table




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

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.