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

[Lyle]

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?

[Anonymous · Accepted solution]

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.

[Lyle]

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

[smpa01]

@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

[AlexisOlson]

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).

[smpa01]

@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

[Lyle]

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/ERldVVcUILdJmn17lr...

https://contraconsolidations-my.sharepoint.com/:x:/g/personal/dylan_contracon_net/EWaLX3HYNVNOiVLzEb...

[AlexisOlson]

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 )
    )