Forum Discussion

ruesaint_denis's avatar
ruesaint_denis
Icon for Helper I rankHelper I
4 years ago

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. 

 

 

date slicers
DAX

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

     

  • ruesaint_denis's avatar
    ruesaint_denis
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.