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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RH-BIP
Helper III
Helper III

Calculate measure based on if statement

Hello!

 

This year I started with PowerBI, i have about 2 years experience with QlikSense/View. So most of the challenges I could solve with my experience with Qlik. But now I just cant get it to work like I want, hope you can help!

 

I want to make the following measure: sum([Amount]) + sum(previousmonth([Amount]) from the FACT table. But when its january 2018 i dont want to sum the previous month 2017 december. So sum only if the previous month is in the current year.... In Qlik i would have done something like...if year from fact table matches the calendar year then sum amount + previousmonth amount.

 

how do i achieve this in PowerBI?

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @RH-BIP,

 

Please refer to this thread to use PREVIOUSMONTH function properly.

 

In your scenario, the IF statement could be similar to:

Measure =
IF (
    MONTH ( SELECTEDVALUE ( 'DateTime'[DateKey] ) ) <> 1,
    SUM ( Fact[Amount] )
        + CALCULATE ( SUM ( Fact[Amount] ), PREVIOUSMONTH ( 'DateTime'[DateKey] ) )
)


For more advice, please provide sample data and desired result as suggested in this article.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @RH-BIP,

 

Please refer to this thread to use PREVIOUSMONTH function properly.

 

In your scenario, the IF statement could be similar to:

Measure =
IF (
    MONTH ( SELECTEDVALUE ( 'DateTime'[DateKey] ) ) <> 1,
    SUM ( Fact[Amount] )
        + CALCULATE ( SUM ( Fact[Amount] ), PREVIOUSMONTH ( 'DateTime'[DateKey] ) )
)


For more advice, please provide sample data and desired result as suggested in this article.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yulgu-msft exactly what i needed!

 

Had to alter it a bit but this works, thank you!

 

Previous = SUM ( 'Fact table'[Amount] ) + IF (
    MONTH ( SELECTEDVALUE ( 'Calender'[Jaar_maand] ) ) <> 1, CALCULATE ( SUM ( 'Fact table'[Amount] ), PREVIOUSMONTH ( 'Calender'[Datum] ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.