DAX total amount for entire months shows in following month

[hafchez]

Hi Everyone,

I'm new to power bi and needs help from power bi community. I have been struggles for weeks to get the desire result.

I have data which I need to interpret to matrix visual to get the result for the total amount from entire previous month which the result should be shows in the following month.

For example, as you can see from below screen shots,

these are the amount for each month filtered by year. I need to get the result which total of entire previous month to display in the following month. The earliest data recorded is on February. So below are the expected result:

The end result needed as per below:

January 2021 result = 0

February 2021 result = 0 (No amount recorded for January)

March 2021 result = 74.9200 (Jan 0 + Feb 74.9200)

April 2021 result= 876.3900 (Feb 74.9200 + Mar 801.4700)
May 2021 result = 894.8100 (Feb 74.9200 + Mar 801.4700 + Apr 18.4200)

then if I filtered year 2022, the end result needed as per below:

January 2022 result = 8,585.3148 (as per total amount recorded from earliest data until 31 December 2021 at 23:59:59)

February 2022 result = 9,662.5048 (total amount from earliest recorded data until 31 January 2022 at 23:59:59)

March 2022 result = 10,236.1648 (total amount from earliest recorded data until 28 February 2022 at 23:59:59)

So instead of showing the amount of current month (as per image), the result for the all following month should be the same calculation method.

I attached herewith the data.

https://1drv.ms/u/s!AlhUeAWUWE5vbPE807wZ4P5puPU 

Thank you

[v-jayw-msft]

Hi @hafchez ,

You could create a measure to get the previous month's value then create another measure to get the cumulative value of the first measure.

You will also need to create an independent year slicer table so that the rest years' data won't be filtered when you select a year.

Best Regards,

Jay

[hafchez]

Hi @v-jayw-msft ,

I have tried the above method but getting error of duplicate date because of the data consist of same date with multiple rows. Is there any chance this can be solved? I have attached the sample data above for your reference. Thank you in advance!

[hafchez]

Hi experts, @amitchandak  , @tamerj1  , @Jihwan_Kim. Any suggestion or solution? Thank you very much in advance.