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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
aidaamieira
New Member

cumulative calculation

 

Hi,
I need help with the following:
I have a database with quarterly results, my objective is to present the quarterly and accumulated results, that is, whoever consults the dashboard could consult the results of one of these options:
1Q 2021;
1Q Accumulated 2021 (1Q 2021);
2Q 2021;
Accumulated 2Q 2021 (1Q 2021 + 2Q 2021)
3Q 2021;
Accumulated 3Q 2021 (1Q 2021 + 2Q 2021 + 3Q 2021)
4Q 2021;
4Q Accumulated 2021 (1Q 2021 + 2Q 2021 + 3Q 2021 + 4Q 2021)
1Q 2022;
Accumulated 1Q 2022 (2Q 2021 + 3Q 2021 + 4Q 2021 + 1Q2022);
2Q 2022;
Accumulated 2Q 2022 (3Q 2021 + 4Q 2021 + 1Q2022 + 2Q2022);
3Q 2022;
Accumulated 3Q 2022 (4Q 2021 + 1Q2022 + 2Q2022 + 3Q2022);
4Q 2022;
4Q Accumulated 2022 (1Q2022 + 2Q2022 + 3Q2022 + 4Q2022);
The problem centered on the fact that a quarter is included in the calculation of the average value of more than one accumulated, for example, the 1st of 2021, it is included in the accumulated of the 1Q of 2021, the 2Q of 2021, the 3Q of 2021 and the 4Q 2021.
The database is updated quarterly.
The only solution I have at the moment is every time a quarter is entered, adding a new metric for the cumulative calculation, or updating the existing metric. However, I would like to put this calculation automatically or in a single metric.

To make it easier, here is an explanation with images.
The database I have is something like this:

Imagem1_1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And what I want to get is something like this:

Imagem2_2.png

The idea is to have the 4 filters (year, quarter, sector and company) with a single selection option for all except the company and add an option to the quarter filter that, when selected, gives the results for the accumulated year as I explained previously.
Is there any possibility?
Thanks in advance.

1 ACCEPTED SOLUTION
sergej_og
Post Prodigy
Post Prodigy

Hey @aidaamieira,
what about TOTALYTD formula?
Smtg like this Acc = TOTALYTD(your measure, Calendar[Date])

Have you already played around with it?

Regards

View solution in original post

2 REPLIES 2
sergej_og
Post Prodigy
Post Prodigy

Hey @aidaamieira,
what about TOTALYTD formula?
Smtg like this Acc = TOTALYTD(your measure, Calendar[Date])

Have you already played around with it?

Regards

Thank you @sergej_og 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.