Get Previous Value using Calculate

[WishAskedSooner]

Hi all,

I have a Fact table and several Dimension tables. I currently have the following measure defined in my Data model:

CALCULATE(SUM('Fact'[Value]), ALL('DimLedger'), 'DimLedger'[ID] = 2)

I put this measure in a card with a slicer to select the Month from the Date Dimension table. It works as expected.

Now, I want to create a second card with a measure that shows the previous month's value based on the selected month in the slicer. I have tried the following, but it just returns a blank:

CALCULATE(SUM('Fact'[Value]), ALL('DimLedger'), 'DimLedger'[ID] = 2, DATEADD('DimDates'[Date], -1, MONTH))

I also tried using the ALL() function, but it just returns the selected month value, not the previous month's value:

CALCULATE(SUM('Fact'[Value]), ALL('DimLedger'), 'DimLedger'[ID] = 2, DATEADD(ALL('DimDates'[Date]), -1, MONTH))

I am stuck as to what I am doing wrong. Any help is appreciated.

[WishAskedSooner · Accepted solution]

I don't know if this is the most elegant solution, but it seems to work. However, if you know of a better solution, please feel free to post.

Prev Month Value = 
    VAR PrevMonth = DATEADD('DimDates'[Date], -1, MONTH)
Return
    CALCULATE(SUM('Fact'[Value]), FILTER(ALL('DimLedger'), 'DimLedger'[ID] = 2), FILTER(ALL('DimDates'), 'DimDates'[Date] = PrevMonth))

[Anonymous]

Hi,@WishAskedSooner 

Congratulations on finding a solution to the problem and having already accepted it as a solution, this is very helpful to our community and having community members with the same problem as you will find a solution much faster. Thank you again for your contribution.

Best Regards,

Leroy Lu