Forum Discussion

powerbi_jenhen's avatar
powerbi_jenhen
Resolver II
3 years ago
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. 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.

Need Help
  • powerbi_jenhen's avatar
    powerbi_jenhen
    3 years ago

    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])))

1 Reply

  • powerbi_jenhen's avatar
    powerbi_jenhen
    Resolver II
    3 years ago

    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])))