Reply
Frequent Visitor
Posts: 3
Registered: ‎02-14-2019
Accepted Solution

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?

 


Accepted Solutions
Highlighted
Community Support Team
Posts: 5,880
Registered: ‎09-21-2016

Re: Calculate measure based on if statement

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


All Replies
Highlighted
Community Support Team
Posts: 5,880
Registered: ‎09-21-2016

Re: Calculate measure based on if statement

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.
Frequent Visitor
Posts: 3
Registered: ‎02-14-2019

Re: Calculate measure based on if statement

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