Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX getting previous value (multiple columns)

Hi,    I have a final table like this: Year Month Name Account Name Contact Full Name Value Value PM Diff 2021-Jan BT Peter 9   9 2021-Aug BT Peter 8 7,89 0,11 2022-Jan ...
Need Help
Show and Tell
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    If you need calculated column:

    Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < EARLIER ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    If you need measure:

    Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Year Month Name] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] < MAX ( 'Table'[Year Month Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Contact Full Name] ),
            [Year Month Name] = _a
        )
    )

    Output:

    Best Regards,

    Jianbo Li

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

amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous , Create a date using month year and join it with date of date table and then you can have measure like 

 

example measures

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

 

Or you can consider offset

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U