Measure with multiple IFs 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-shex-msft · Accepted solution]

HI @Anonymous,

It seems like you want to find out the previous date to calculate with your measure formula based on current date, you can try to use the following measure if it meets for your requirement:

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

If above not help, please share some dummy data and expected results to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft  Hi Xiaoxin, thanks for taking time to help me. this works beautifully!  

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