Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Previous Month value in a dataset

Hi All,

Pls guide.

My dataset - I tried using dateadd (but my case does not have date field) - I am able to get previous month no using DAX but not the value corresponding to it. 

MOM =
VAR V1 = MAX(MOM[Month]) -1.
VAR V2 = CALCULATE ( sum(MOM[Amount]), filter (MOM, MOM[Month] = V1 ))
return V2 -- this does not return anything in that field.. Pls help.

Source

YearMonthAmount
202111
 

2

2
 33
 44
 55
 66
 77
 88
 99
 1010

target

YearMonthAmtPreviousMonth
202111 
 221
 332
 443
 554
 665
 776
 887
 998

 

Thank you

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    You can extract the current month and year field value from the current row context, then you can use the date function to concatenate them as a date value to calculate the previous date.
    After these steps, you can extract the year and month part values from the variables as conditon to calcualte them in your expression.

    PrevAmount =
VAR currMonth =
    MAX ( MOM[Month] )
VAR currYear =
    MAX ( MOM[Year] )
VAR prevDate =
    DATE ( currYear, currMonth - 1, 1 )
RETURN
    CALCULATE (
        SUM ( MOM[Amount] ),
        FILTER (
            ALLSELECTED ( MOM ),
            MOM[Month] = MONTH ( prevDate )
                && MOM[Year] = YEAR ( prevDate )
        )
    )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    You can extract the current month and year field value from the current row context, then you can use the date function to concatenate them as a date value to calculate the previous date.
    After these steps, you can extract the year and month part values from the variables as conditon to calcualte them in your expression.

    PrevAmount =
VAR currMonth =
    MAX ( MOM[Month] )
VAR currYear =
    MAX ( MOM[Year] )
VAR prevDate =
    DATE ( currYear, currMonth - 1, 1 )
RETURN
    CALCULATE (
        SUM ( MOM[Amount] ),
        FILTER (
            ALLSELECTED ( MOM ),
            MOM[Month] = MONTH ( prevDate )
                && MOM[Year] = YEAR ( prevDate )
        )
    )

    Regards,

    Xiaoxin Sheng