topic Re: DAX total amount for entire months shows in following month in DAX Commands and Tips

DAX total amount for entire months shows in following month

hafchez — Tue, 06 Sep 2022 05:39:57 GMT

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

Re: DAX total amount for entire months shows in following month

hafchez — Wed, 07 Sep 2022 01:17:40 GMT

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

Re: DAX total amount for entire months shows in following month

Anonymous — Wed, 07 Sep 2022 08:59:02 GMT

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

Re: DAX total amount for entire months shows in following month

hafchez — Thu, 08 Sep 2022 03:34:38 GMT

Hi Anonymous ,

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!