Skip to main content
cancel
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.

Reply

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. 

ruesaint_denis_0-1656455231493.png

 

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. 

 

 

4 REPLIES 4
v-yalanwu-msft
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))
v-yalanwu-msft
Community Support
Community Support

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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.