DAX getting previous value (multiple columns)

[Anonymous]

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	BT	Peter	9	7,53	1,47
2022-Jan	BT	Albert	7	7,53	-0,53
2022-Aug	BT	Peter	9	8,42	0,58
2022-Aug	BT	Albert	6	8,42	-2,42

and what I want to do is, getting the previous row value as shown below.
There are multiple columns "Year Month Name", "Account Name" and "Contact Full Name".

I want to get previous values according that columns. How can I handle this?

I created a measure that is working well just for Year Month Name (the [Value PM] values that I'm showing are fine if you put only the Year month Name column, but if I add the other two, the values ​​don't look correct, because they are the same values ​​for Year Month Name, is only considers the Year Month Name column), but I think I need to add the Account Name and Contact Full Name columns to the logic:

Value PM: 

CALCULATE (
    [Value],
    FILTER (
        ALL ( Table1 ),
       'Table1'[Date Key]
            = CALCULATE (
                MAX (   'Table1'[Date Key]),
                FILTER ( ALL ( 'Table1'  ),   'Table1'[Date Key]< SELECTEDVALUE (   'Table1'[Date Key] ) )
            )
    )
)

The Value measure is a simple average measure:

Value Measure = CALCULATE(
AVERAGE([Value]),
FILTER('Table1], 'Table1'[KPI] = "KPI 2")
)

Excepted output:

 Thanks a lot !

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = LOOKUPVALUE(Data[Value],Data[Year Month Name],CALCULATE(MAX(Data[Year Month Name]),FILTER(Data,Data[Contact Full Name]=EARLIER(Data[Contact Full Name])&&Data[Year Month Name]<EARLIER(Data[Year Month Name]))),Data[Contact Full Name],Data[Contact Full Name])

Hope this helps.

[v-jianboli-msft · Accepted solution]

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.

[anvikuttu]

Hi @v-jianboli-msft 

How do we group the above example under "Year Month  Name " field. I mean keep this field and exclude other fields. Is it possible to sum the values shown by Measure without losing the filter context. For eg Aug 2022 should show 9+7= 16 ?

[amitchandak]

@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