Forum Discussion
Solved
Calculate Previous Month Values
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. sh...
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])))
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])))