Measure with multiple IF statements in RETURN

[Anonymous]

Hi guys,

I need to show one index ([Öffnungsrate Measure]) for the current (or selected) month and either the previous month or, if there is no data previous month, for the month before that. If no data again, than three month ago etc.  I wrote a measure for the last month ([ÖR last month]) and another measure with variables for -2 months, -3months etc. What I want this second measure to do is to show the value for the last month when there is data. When no data last month, then -2 months. if no data again, then -3 months etc. I wrote therefore multiple IFs in RETURN. However, it works only when the first IF is true ( so maximum two months back). If the first data comes only three months back, it doesn't show it. Can I use multiple IFs in RETURN at all? Is there an alternative? Any help would be greatly appreciated.

Cheers, Tanja

Measure 1:

ÖR last month = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -1; MONTH))

Measure 2: 

ÖR previous period =

Var zweimonate= CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -2; MONTH))

Var dreimonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -3; MONTH))

Var viermonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -4; MONTH))

Var fuenfmonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -5; MONTH))

Return

IF(ISBLANK(cdi_emailsendBase[ÖR last month]); zweimonate;

IF(ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate); dreimonate;

IF(ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate)&&ISBLANK(dreimonate); viermonate;

IF(ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate)&&ISBLANK(dreimonate)&&ISBLANK(viermonate); fuenfmonate;

cdi_emailsendBase[ÖR last month]))))

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You don't need to run IF() in each month. You could return the max month which is not blank and before current month.

Here is a solved case for your reference.

https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/m-p/492501

[Anonymous]

Hi @v-eachen-msft 

Thank you for your reference, that's a very useful post!  It corresponds to the suggestion of another Community Support team member:

ÖR previous period = VAR currDate = MAX ( DatumTabelle[Date] ) VAR prevDate = CALCULATE ( MAX ( DatumTabelle[Date] ); FILTER ( ALLSELECTED ( DatumTabelle ); [Date] < currDate && [Öffnungsrate Measure] <> BLANK () ) ) VAR prevAmount = CALCULATE ( [Öffnungsrate Measure]; FILTER ( ALLSELECTED ( DatumTabelle ); [Date] = prevDate ) ) RETURN IF ( prevAmount <> BLANK (); prevAmount; [ÖR last month] )

Thank you for your assistance!

Cheers, Tanja

[amitchandak]

Try Switch with true

https://community.powerbi.com/t5/Desktop/Help-with-SWITCH-TRUE-with-multiple-items-to-evaluate/td-p/...

https://docs.microsoft.com/en-us/dax/switch-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

@amitchandak  Thank you for your quick reply! I've tried what you suggested (see below) but it still doesn't work. it still shows the outcome only if the first condition is met (...ISBLANK(cdi_emailsendBase[ÖR last month]); zweimonate;). 

ÖR previous period =

Var zweimonate= CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -2; MONTH))

Var dreimonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -3; MONTH))

Var viermonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -4; MONTH))

Var fuenfmonate = CALCULATE([Öffnungsrate Measure]; DATEADD(DatumTabelle[Date]; -5; MONTH))

Return

SWITCH(

TRUE();

ISBLANK(cdi_emailsendBase[ÖR last month]); zweimonate;

ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate); dreimonate;

ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate)&&ISBLANK(dreimonate); viermonate;

ISBLANK(cdi_emailsendBase[ÖR last month])&&ISBLANK(zweimonate)&&ISBLANK(dreimonate)&&ISBLANK(viermonate); fuenfmonate;

cdi_emailsendBase[ÖR last month])