Query on Cumulative totals and relative date slicer

DAX DAX Calculations
1

Hi All,

I want to visualise the no of customers cumulatively, 1 month prior to today - say if today is 16/08/2019 ,
then I want to show cumulative data from 16/07/2019 untill 15/08/2019.
I wrote some DAX for this :

Customers MTD = CALCULATE([Customers],FILTER(ALL(‘Date’[Date]),‘Date’[Date]< = MAX(‘Daily Balances’[Effective Date])))

Customers = IF( ISBLANK( COUNTROWS(Customers))=true, 0, COUNTROWS(Customers))

MAX(‘Daily Balances’[Effective Date]) is the Max date of my Report.

Then I added a relative date filter which filters Last 1 month.

I am based in Australia, in the afternoons , I have noticed that the report shows value for another day, due to different timezone which is linked to my Microsoft office 365 settings and cannot be modified.This makes the report incorrect.

If I set the include todays date to off in the relative date slicer,then the report is fine for the first half of the morning as it would display cumulative counts untill 15/08/2019,
but later during the day it would show value for 16/08/2019, which would be incorrect.

How do i set it to always have the previous day as last day which is Max date of my report always , without toggling the on off on the slicer.

I have attached the pbix file for your reference.

Thanks,

Customers.pbix (133.1 KB)

2

Ok what I would actually do here is quite simple.

First use time intelligence to jump back one day.

Customers - Adjusted = 
CALCULATE( [Customers], DATEADD( 'Date'[Date], -1, DAY ) )

You see now that I have bought the results forward one day.

Then this

Customers MTD Adj = 
CALCULATE( [Customers - Adjusted],
    FILTER(ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date] ) ) )

And that’s it.

One thing, make sure to use the date column here not this column

image
image.png900×152 12.2 KB

Here’s the end result

image
image.png922×629 22.6 KB

3

Hi,

Thank you for your response. Is there a way to do this without using a Date slicer? By just using DAX Measures?

4

The date slicer doesn’t matter really.

The DAX measures get you what you need regardless.

The slicer is just placing some context of the visual

Sam