Forum Discussion

SirBI's avatar
SirBI
Frequent Visitor
3 years ago
Solved

Previous Month Value

I have the below DEX (New Column) to find the previous value based off text date and value. See example table below. 

 

PreviousMonthlyValue =
MAXX (
FILTER (
'Monthly Request',
'Monthly Request'[RequestBucket] = EARLIER ( 'Monthly Request'[RequestBucket])
&& 'Monthly Request'[YearMonth]
= EARLIER ( 'Monthly Request'[YearMonth] ) - 1
),
'Monthly Request'[totalCount]
)
 
This works until I get to December and previous value of Jan is not found.
 
How can I resolve this issue?
 
Is this something do do with the ordering of Year Month going from 202201>202112?
 
YearMonth (Whole Number)PreviousMonthlyValueRequest Bucket (Text)TotalCount (Whole Number)
20220225,000Red40,000
2022028,000Green12,000
202201 Red25,000
202201 Green8,000
20221250Red14,000
20221210Green2,000
202211(Null)Red50
202211(Null)Green10
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi SirBI ,

    Please update the formula of the calculated column [PreviousMonthlyValue] as below and you can get the expected result...

    PreviousMonthlyValue =
VAR _premonth =
    CALCULATE (
        MAX ( 'Monthly Request'[YearMonth] ),
        FILTER (
            'Monthly Request',
            'Monthly Request'[RequestBucket] = EARLIER ( 'Monthly Request'[RequestBucket] )
                && 'Monthly Request'[YearMonth] < EARLIER ( 'Monthly Request'[YearMonth] )
        )
    )
RETURN
    MAXX (
        FILTER (
            'Monthly Request',
            'Monthly Request'[RequestBucket] = EARLIER ( 'Monthly Request'[RequestBucket] )
                && 'Monthly Request'[YearMonth] = _premonth
        ),
        'Monthly Request'[totalCount]
    )

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi SirBI ,

    Please update the formula of the calculated column [PreviousMonthlyValue] as below and you can get the expected result...

    PreviousMonthlyValue =
VAR _premonth =
    CALCULATE (
        MAX ( 'Monthly Request'[YearMonth] ),
        FILTER (
            'Monthly Request',
            'Monthly Request'[RequestBucket] = EARLIER ( 'Monthly Request'[RequestBucket] )
                && 'Monthly Request'[YearMonth] < EARLIER ( 'Monthly Request'[YearMonth] )
        )
    )
RETURN
    MAXX (
        FILTER (
            'Monthly Request',
            'Monthly Request'[RequestBucket] = EARLIER ( 'Monthly Request'[RequestBucket] )
                && 'Monthly Request'[YearMonth] = _premonth
        ),
        'Monthly Request'[totalCount]
    )

    Best Regards

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    SirBI Well, if you have an actual date column available you can use EOMONTH. If not, you could use RIGHT to check the last 2 digits and if they are 01 then subtract 89