YTD Cumulative SUM of a measure

[adambalshaw]

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

[Greg_Deckler]

@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

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.

[adambalshaw]

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!

[SpartaBI · Accepted solution]

@adambalshaw not sure how your data looks like before but basically it's something like that:
CALCULATE(
SUMX(
    VALUES(DimDate[PeriodName]),
    [Close Calls By Headcount]
    ),
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

Showcase Report – Contoso By SpartaBI



      

[adambalshaw]

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.

[SpartaBI]

@adambalshaw my pleasure 🙂
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