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.
Customers.pbix (133.1 KB)