Calculate % over total of previous month in table

[annamarie]

Hi
I need some help with fancy DAX please. I need to calculate a percentage with Number of accounts by Category over a specifically calculated total in the previous month.
I bit difficult to explain, I’ve attached an excel file the will show what I’m after. I’m slicing by Company and Monthly date in the report. I’m really only interested in the high-lighted grouping, the rest can be grouped or ignored.

ENDA Level % calc sample.pbix (51.9 KB)Extract.xlsx (77.3 KB)ENDA Level % calc sample.pbix (111.1 KB)

[Melissa]

Hi @annamarie,

First you’ll need a proper Date table to perform any Time Intelligence calculation…
Please incorporate that into your model

Here’s a link to the Extended Date table M code function.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

Next I would suggest to work through these courses.

Ultimate Beginners Guide to DAX

and

Time Intelligence Calculations

I hope this is helpful

[annamarie]

Hi Melissa, apologies I do actually have in my actual report, just forgot to add it in the sample file. I’ve updated the sample now.

[Melissa]

Hi @annamarie,

Thanks for providing that PBIX file.

Couple of things to note, your Date table doesn’t meet some of the criteria for a proper Date table (for example it doesn’t cover full years) and wasn’t Marked as a Date table (you’ll find that option on the Table Tools ribbon)
In the Extended Date table topic you’ll find more on Date table requirements.

Next you’ve created an implicit measure by dragging a numeric field into the table visual values section. I can’t emphisize enough that it is important to always create basic Measures instead. Like:

Num of accounts = SUM( 'Extract'[Number of accounts] )
.

One of the most versatile time intelligence functions is DATEADD

Num of accounts PM = 
CALCULATE( [Num of accounts],
    DATEADD( 'Calendar table'[Date], -1, MONTH )
)

.

And for the % change

% MoM = DIVIDE( [Num of accounts], [Num of accounts PM] )

.
With this result, these don’t match your example but are correct based on the provided data.

Please work through the recommended courses.
Here’s your sample file. ENDA Level % calc sample.pbix (110.5 KB)

I hope this is helpful.

[annamarie]

Thank you so much, however the trick is that the Denominator is actually from a different grouping in the previous month. Like in the excel example. That is where I’m having issues.

[Melissa]

Right, that wasn’t clear to me from your description. Sorry about the misunderstanding.

Give this a go.

Num of accounts PM/PL = 
VAR cLevel = VALUES( 'Extract'[Level Combo] )
VAR pLevel = CALCULATETABLE( VALUES( 'Extract'[PreviousCycleLevel] ), cLevel )
RETURN

CALCULATE( [Num of accounts],
    DATEADD( 'Calendar table'[Date], -1, MONTH ),
    REMOVEFILTERS( 'Extract'[Level Combo] ),
    pLevel
)

I hope this is helpful