Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hafchez
Frequent Visitor

DAX total amount for entire months shows in following month

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,

 

hafchez_1-1662442709620.png

 

hafchez_0-1662442677730.png

 

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

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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
Frequent Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors