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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors