cancel
Showing results for
Did you mean:
New Member

## YTD Cumulative SUM of a measure

Hey,

I've got a measure [Frequency Rate] which is just DIVIDE([EventCount], [Headcount]) and I need to work out what the [Frequency Rate] is for each month (see table below) and then SUM the measure for all the months YTD.

The figure I need to get to for the measure from the table would be 2.06 but I just keep banging my head against a wall on this one.

I've got DAX to figure out the table bit but I can't SUM the measure - any ideas? Hope that makes sense!

------

EVALUATE

SUMMARIZECOLUMNS(
DimDate[PeriodName],
FILTER(DimDate, DimDate[IsCurrentYear] = TRUE()),
"Frequency Rate", [Close Calls By Headcount]
)

which gives:

 PeriodName Frequency Rate 01/01/2022 00:00:00 0.189374262101535 01/02/2022 00:00:00 0.210484511517077 01/03/2022 00:00:00 0.22818523554347 01/04/2022 00:00:00 0.201570680628272 01/05/2022 00:00:00 0.226923692209247 01/06/2022 00:00:00 0.208005834211166 01/07/2022 00:00:00 0.108861796014921 01/08/2022 00:00:00 0.683333333333333
1 ACCEPTED SOLUTION
Community Champion

@adambalshaw not sure how your data looks like before but basically it's something like that:
CALCULATE(
SUMX
(
VALUES(DimDate[PeriodName]),
),
DimDate[IsCurrentYear] = TRUE()
)

If you can't take it from here then share a sample file.
Anyways you should never use summarizecolumns in measure(only for queries / calculated tables). If you need the same technique then for measures you use the ADDCOLUMNS(SUMMARIZE(....)) pattern

5 REPLIES 5
Super User

@adambalshaw So it should essentially be a SUMX statement with a SUMMARIZECOLUMNS with a FILTER for all prior months in the current year. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Hard to be specific without sample data. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
New Member

Thanks for replying! Yeah it was SUMX I needed to be using. And I've had a look through those links and I think they're very helpful - I'll definitely have a read through properly when I've got a minute in preparation for my next time intelligence issue!

Community Champion

@adambalshaw not sure how your data looks like before but basically it's something like that:
CALCULATE(
SUMX
(
VALUES(DimDate[PeriodName]),
),
DimDate[IsCurrentYear] = TRUE()
)

If you can't take it from here then share a sample file.
Anyways you should never use summarizecolumns in measure(only for queries / calculated tables). If you need the same technique then for measures you use the ADDCOLUMNS(SUMMARIZE(....)) pattern

New Member

Thanks for the reply, that was exactly it! Not sure why SUMX didn't occur to me but maybe I need to look further into in.

Community Champion

Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors