How to get previous cummulative count for rolling 30 days

[gauri]

Hello,

In the image below i have rolling 30 days visual in which, in the column values i have count of last 30 days, this means i had 42 residents on 6th of may  and 48 residents on 21st may and so on. Here i want to calculate change between rolling 30 days count and its previous count. for eg: on 5th may 2020 i have a count of 43 and its previous day count i.e on 4th may 2020 is 42 , so my change would be 43-42 i.e 1 which which would be displayed in the line values. This will be applied for every value. Here 30th day is 3rd may 2020 so it should fetch previous day and its count.

to calculate this daily count i have used these following measure :

Average daily count = AVERAGEX(VALUES(census_pcc_new[CensusDate]), [row count])

census client count last 30 days =

var last_day = TODAY()

return

CALCULATE([Average daily count],

FILTER(census_pcc_new, census_pcc_new[CensusDate] > last_day- 30 && census_pcc_new[CensusDate] <= last_day ),FILTER(census_pcc_new, census_pcc_new[FacilityID]))

How can i get previous count and calculate the change.

Thanks

[gauri]

It would be great if anyone helps me out with this issue, as it is very urgent.

Thanks,

[v-diye-msft]

Hi @gauri 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[gauri]

@v-diye-msft There are measures present in the visual , nad creating a sample pbix would take time and this issue is really urgent, so it will not be possible to send a pbix . Other than this i can provide information by using we can solve this issue

[Tahreem24]

Hi @gauri ,

Try the below DAX:

Subtraction Measure=

VAR last_30day = CALCULATE([Average daily count],

FILTER(census_pcc_new, census_pcc_new[CensusDate] > TODAY()- 30 && census_pcc_new[CensusDate] <= TODAY() ),FILTER(census_pcc_new, census_pcc_new[FacilityID]))

VAR last_31days = 

CALCULATE([Average daily count],

FILTER(census_pcc_new, census_pcc_new[CensusDate] > TODAY()- 31 && census_pcc_new[CensusDate] <= TODAY() ),FILTER(census_pcc_new, census_pcc_new[FacilityID]))

RETURN  last_30day- last_31day

[gauri]

Hi @Tahreem24 

i tried your  measure. its giving me output like this :

here everywhere its showing 0 , so places where 30 days count is 43 and its previous count is 42 it should give me 1, and in this visual, census change is the subtraction measure which you had sent me.

[amitchandak]

@gauri , try like

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))

Rolling 30 day 1 day before = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date])-1,-31,Day))

[gauri]

@amitchandak 

i tried the above measure but i am not able to get a proper count of previous day and also all the counts are not displaying for today -30 days which were first displaying in the visual.

4th may 2020 should take previous value as 41 , its taking 42