Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate previous month value based on selected dates

I have following data set:

DateValue
1.2.2019400
15.2.201950
23.2.2019100
24.2.2019150
5.3.201980
19.3.201920
23.3.201935
25.3.201970
1.4.201915

 

I like to give end user a change to filter dates and after that calculate value for the last month... based on selected values:

 

 ValuePrevMonth
23.2.2019100 
24.2.2019150 
5.3.201980250
19.3.201920250
1.4.201915100

 

I have been trying to do this many ways already and don't seem to get it to work. Can anyone help me with this?

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

     

    You may try below measure:

    Measure = 
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        MONTH ( Table1[Date] )
            = MONTH ( MAX ( Table1[Date] ) ) - 1
    )
)

    Regards,

    Cherie

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

     

    You may try below measure:

    Measure = 
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        MONTH ( Table1[Date] )
            = MONTH ( MAX ( Table1[Date] ) ) - 1
    )
)

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-cherch-msft ,

       

      It is not work when we have 2 years of data i.e., 2018 and 2019. Is is working when we have 1 year data alone. It is getting summed up for nov month 2019 as well as 2018 whenever the month value is returning as 11.

       

      Thanks