IF current week is blank show prior week and if prior week is blank so Not Available

[Anonymous]

Hi All,

I am having difficulty finding a solution to a simple logic measure. The measure will be placed in the KPI visual. The logic is as follows:

Two measures:

Current Week

Prior Week

Logic:

If the current week's results are blank, show the prior week's results. If the prior week's results are blank, show "Not Available".

The reason the current week's results would be blank is because a portion of our data is not updated until the end of the week and everything else is updated at the beginning of the week. The reason the prior week would be blank is again the same reason except the lag between updates may move into following week.

Thank you!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Measure = 
VAR _selweek =
    SELECTEDVALUE ( 'Date'[Week] )
VAR _sales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        WEEKNUM ( 'Table'[Date], 2 ) = SELECTEDVALUE ( 'Date'[Week] )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALL ( 'Date' ), NOT ( ISBLANK ( _sales ) ) )
    )
VAR _pwsales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALLSELECTED ( 'Table' ), WEEKNUM ( 'Table'[Date], 2 ) = _selweek - 1 )
    )
RETURN
    IF (
        _selweek < WEEKNUM ( _mindate, 2 ),
        BLANK (),
        IF (
            ISBLANK ( _sales ) && ISBLANK ( _pwsales ),
            "Not Available",
            IF ( ISBLANK ( _sales ), _pwsales, _sales )
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Anonymous , you have few columns in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

and then you can have measure like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

The final measure can be 

if(isblank([This Week]), if(isblank([Last week]), "Not Avaiable", [Last Week]&""), [This Week]&"")

But this will make your measure as text measure