Calculate Previous Month Values

[powerbi_jenhen]

Hi,

I have the below FACT table:

I'm looking to show the previous month "AMOUNT" values based on the unique "ContractEntityKey" values (this has a relationship to a DIM table), i.e. show the January values within the February rows. Each month shares the same row count of keys (so the same DIM details), I just need need to perform a row calculation to produce a variance value. I've used the PreviousMonth function but I get blanks.

Any help appreciated.

Thanks.

[powerbi_jenhen · Accepted solution]

I actually had a bad DIM table, once I added the correct unique DIM key to the FACT table and added a Dates table the below Calculated Columns worked:

Date PM Col = DATEADD(Dates[Date],-1,MONTH)

PM Amount = CALCULATE(MAX(Fact[AMOUNT]),FILTER(ALL(Fact),Fact[DimContractEntityKey]=EARLIER(Fact[DimContractEntityKey])&&Fact[Date]=EARLIER(Fact[Date PM Col])))