topic Re: Measure from current month & year, to the same month from the previous year in DAX Commands and Tips

Measure from current month & year, to the same month from the previous year

Lyle — Mon, 29 Nov 2021 13:30:59 GMT

I'm trying to create a DAX measure for the growth percentage like this:

I have data for 3 years but when using filters in PBI for Trade 1 and Trade 2 I can't figure out the formula.

Is this possible?

Re: Measure from current month & year, to the same month from the previous year

smpa01 — Mon, 29 Nov 2021 14:10:31 GMT

Lyle you can achieve what you need with this, pbix is attached

Measure = VAR _00 = MAX ( 'Table'[Count] ) VAR _0 = CALCULATE ( CALCULATE ( MAX ( 'Table'[Count] ), 'Table'[Count] < _00 ), ALLEXCEPT ( 'Table', 'Table'[Trade1], 'Table'[Trade2] ) ) VAR _1 = DIVIDE ( MAX ( 'Table'[Count] ) - _0, _0 ) RETURN _1

Re: Measure from current month & year, to the same month from the previous year

AlexisOlson — Mon, 29 Nov 2021 16:28:40 GMT

This works for the given example but has a hidden assumption built in that Count is increasing every year. I think you meant to use your index column instead of Count for purposes of sorting (typically, you'd use a date dimension table for this).

Re: Measure from current month & year, to the same month from the previous year

smpa01 — Mon, 29 Nov 2021 16:34:25 GMT

AlexisOlson you are right. In that context, OP needs to add an index column and use the following instead

Measure = VAR _00 = MAX ( 'Table'[Index] ) VAR _0 = CALCULATE ( CALCULATE ( MAX ( 'Table'[Count] ), 'Table'[Index] < _00 ), ALLEXCEPT ( 'Table', 'Table'[Trade1], 'Table'[Trade2] ) ) VAR _1 = DIVIDE ( MAX ( 'Table'[Count] ) - _0, _0 ) RETURN _1

Re: Measure from current month & year, to the same month from the previous year

Lyle — Wed, 01 Dec 2021 07:38:19 GMT

Thanks smpa01 AlexisOlson I tried this, please see my sample documents.

2019 shouldn't have a % as there's no data for 2018 and the 2020 & 2021 % are not working.

https://contraconsolidations-my.sharepoint.com/:u:/g/personal/dylan_contracon_net/ERldVVcUILdJmn17lrSmtr4BQn-2hnn0dc6D8nEIT0b0zQ?e=tx35T6

https://contraconsolidations-my.sharepoint.com/:x:/g/personal/dylan_contracon_net/EWaLX3HYNVNOiVLzEb5kt30B02VCQn2bdsb7iuAAN-g07A?e=BO0zdE

Re: Measure from current month & year, to the same month from the previous year

AlexisOlson — Wed, 01 Dec 2021 17:22:43 GMT

I think this might be closer to what you're after:

Growth = VAR CurrSum = SUM ( Box[Trips] ) VAR PrevSum = CALCULATE ( SUM ( Box[Trips] ), DATEADD ( 'Calendar'[Date], -1, YEAR ) ) RETURN IF ( CurrSum > 0 && PrevSum > 0, DIVIDE ( CurrSum - PrevSum, PrevSum ) )

Re: Measure from current month & year, to the same month from the previous year

Anonymous — Thu, 02 Dec 2021 05:41:22 GMT

Hi Lyle ,

I think you don't need to add an index column to calculate the growth rate. You can try my measure.

Growth = VAR _Current = SUM ( Box[Trips] ) VAR _LASTYEAR = CALCULATE ( SUM ( Box[Trips] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) - 1 && 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) ) ) VAR _DIFF = _Current - _LASTYEAR VAR _GROWTH = DIVIDE ( _DIFF, _LASTYEAR ) VAR _MINDATE = EOMONTH ( MINX ( ALL ( Box ), Box[ETA] ), 11 ) VAR _MAXDATE = EOMONTH ( MAXX ( ALL ( Box ), Box[ETA] ), 0 ) RETURN IF ( MAX ( 'Calendar'[Date] ) <= _MINDATE || MAX ( 'Calendar'[Date] ) > _MAXDATE, BLANK (), _GROWTH )

Here I use mindate (2019/12/31 the last day of first year) and maxdate(2021/11/30 the last day of current month) as a filter in IF function, we will get growth in dates between the range, out the range will show blank.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure from current month & year, to the same month from the previous year

Lyle — Tue, 07 Dec 2021 09:06:50 GMT

Thanks Anonymous ! Absolutley spot on. Wow this is amazing. Thank you so much.