Dynamic Date Range Slicer conflicting with outside period references

DAX DAX Calculations
1

Hello,

I implemented this solution to create a relative date slicer on my model. However, once I did this, it effectively made all my “previous month” calculations as “blank” when selecting for current month to date.

I’m assuming this is because the slicer filters the previous month out of the date table, and so there is no data available for the DAX measure to reference. I attempted to use functions such as “All”, however, I still couldn’t get my previous month values to populate.

For example, I have the following DAX measure to calculate membership levels:

My “current period” function:

Last MemCnt =
IF (
    LASTDATE ( Dates[Date] ) >= TODAY (),
    CALCULATE (
        [Avg MemCnt],
        LASTNONBLANK ( 'Memberships'[date], MAX ( Dates[Date] ) )
    ),
    CALCULATE ( [Avg MemCnt], LASTDATE ( Dates[Date] ) )
)

Previous month function:

Last MemCnt EOLM =
CALCULATE ( [Last MemCnt], PREVIOUSMONTH ( Dates[Date] ) )

Can someone please advise?

Thank you,
Curtis

2

Hi @cbchow, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @cbchow

Your understanding is correct that applying slicer filters the previous month out of the date table, and so there is no data available for the DAX measure to reference.

Here, I am assuming you are trying “ALL” on Date Table. This will not work as Date table is already filtered by Period table. To Rectify this, add ALL(Periods[Period]) or ALL(Periods) as shown below. I don’t have your PBIX to try on your Calculations.

Total Sales LM _All =
CALCULATE([Total Sales],ALL(Periods),PREVIOUSMONTH(Dates[Date]) )

Total Sales LM_DateAdd =
CALCULATE([Total Sales],ALL(Periods[Period]),DATEADD(Dates[Date],-1,MONTH))

Periods

This is something I find difficult to understand and Explain.

  1. Above will retain filter on the visual i.e. returning Dates based on Period Slicer i.e. for December, only 7 days are returned.
  2. While Evaluating Calculate , ALL(Periods) will remove all Filters from Period table to Date table. Thus, for Calculate all days will be there.
  3. PreviousMonth() - As it is Second Filter on Calculate, it will take only Filtered Dates from visual and All dates while Calculating previous month, hence giving correct values.

Let me know if you face any issues with your Model.

Thanks
Ankit J

1 Like
4

This worked perfectly, thank you!

Side note: Is there a difference between using “Previous Month” and “DateAdd -1 month”?

5

Hi @cbchow - PreviousMonth gives cumulative as you can see in Image 1. DateAdd gives based on Dates only as in Image 2.

Thanks
Ankit J

1 Like