# Calculate % over total of previous month in table

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)

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.

Next I would suggest to work through these courses.

and

I hope this is helpful

1 Like

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.

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.

1 Like

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.

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