Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
powerbi_jenhen
Resolver II
Resolver II

Calculate Previous Month Values

Hi,

 

I have the below FACT table:

powerbi_jenhen_0-1688118075482.png

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.

1 ACCEPTED SOLUTION
powerbi_jenhen
Resolver II
Resolver II

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

View solution in original post

1 REPLY 1
powerbi_jenhen
Resolver II
Resolver II

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.