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

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