cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
guilherme_gq
Helper I
Helper I

Calculate YTD based in other YTD

Hi!

Im trying to calculate a measure (Measure Total) based in other measure. Its something like that:

 

 

VALUES.png

 

The measure "Monthly HeadCount" is a value for each month. The Measure "Measure Acum Month"  is the sum of "Monthly HeadCount" based in TOTALYTD, considering April for the first month in year.

 

 

Measure Acum MonthTOTALYTD([Monthly HeadCount];'Calendar'[Date];FILTER(ALL('Calendar'[Date]);'Calendar'[Date]<=MAX('Calendar'[Date]));"31/03")

 

 

My problem is the last measure, "Measure Total". I need the value to be a sum of "Measure Acum Month" based in TOTALYTD, the same way that "Measure Acum Month" is calculated.

 

For Example:

 

For the first row of grid, the value for the "Measure Total" is 300. But for second row should be 910 (300+610), the third should be 1820 (300 + 610 + 910)...

 

Someone have any idea how i calculate this?

 

 

Link for download:

https://1drv.ms/f/s!ArRCaJa39TWNgec7wzfYNI09uybTPA

 

1 ACCEPTED SOLUTION
guilherme_gq
Helper I
Helper I

Thanks for all!

 

I solved the question using SUMMARIZECOLUMNS, creating a new table based in other Calculate:

 

SUMMARIZECOLUMNS(DimDate[Year];DimDate[MonthNumber];"TotalValueCum";[Total Value **bleep**];"Total Count **bleep**";[Total Count **bleep**])

 

 

Now, i have a new table with values cumulateds, for new calculations.

View solution in original post

8 REPLIES 8
guilherme_gq
Helper I
Helper I

Thanks for all!

 

I solved the question using SUMMARIZECOLUMNS, creating a new table based in other Calculate:

 

SUMMARIZECOLUMNS(DimDate[Year];DimDate[MonthNumber];"TotalValueCum";[Total Value **bleep**];"Total Count **bleep**";[Total Count **bleep**])

 

 

Now, i have a new table with values cumulateds, for new calculations.

Stachu
Community Champion
Community Champion

out of curiosity - is this an acadmic question, or is there some business logic behind it?

I think it should be possible to do it with SUMX & SUMMARIZE, but I'm really curious why it needs to be done



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Hi, Stachu!

Its a business logic, to create a specific KPI for client. But is not a final value. Has to be used in others calculations.

but if I get it right then some months are actually multiplicated, right?
from your example
For the first row of grid, the value for the "Measure Total" is 300. But for second row should be 910 (300+610), the third should be 1820 (300 + 610 + 910)...
in the third row 300 has been included 3 times, while the actual value for that month was 300
1820 = 300 + (300 + 610) + (300 + 610 + 910)
can you elaborate on this business logic?, maybe there will be other way of achieving the same result?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Exactly, Stachu!

 

in the third row 300 has been included 3 times, while the actual value for that month was 300
1820 = 300 + (300 + 610) + (300 + 610 + 910)

 

 

 

For now, i use this value to return a average between months. But Average is calculated based in the sum of this values.

 

Its something like:

                YTD       AVERAGE

JAN          300        300   (300/ 1 Month)

FEB           610        455   (300+610/2 Months)

MAR         910        606   (300+610+910/3 Months)

Hi @guilherme_gq,

Please review this similar thread: https://community.powerbi.com/t5/Desktop/Recursive-query/td-p/234698.

Best Regards,
Angelia

v-huizhn-msft
Microsoft
Microsoft

Hi @guilherme_gq,

You use the following formula, which doesn't work?

Measure Total = TOTALYTD([Measure Acum Month];'Calendar'[Date];FILTER(ALL('Calendar'[Date]);'Calendar'[Date]<=MAX('Calendar'[Date]));"31/03")


Do you mind share your .pbix file for better analysis?

Best Regards,
Angelia

Angelia,

I edited my first post. Link is there!

 

And create a new measure based in CALCULATE, if the YTD measure create any problem.

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors