cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chimsie
Helper II
Helper II

LASTNONBLANK for max 3 days


I have a table which collected data from a realtime sources, sometimes it happens that the collections fails for some reason on some assets, for the missing values I use below DAX (LASTNONBLANK) to fill the blanks with the most recent value at that point in time.

calc value = 
    var assetid = [AssetID]
    var curDate = [Date]
    var priorMaxDate = CALCULATE(MAX('Table1'[Date]), FILTER('Table1', 'Table1'[AssetID] = assetid && [Date] < curDate && 'Table1'[Value] <> BLANK()))
return 
   if([Value] = blank(), CALCULATE(max('Table1'[Value]), FILTER('Table1', [AssetID] = assetid && [Date] = priorMaxDate)), 'Table1'[Value])

 

Now I want to expand the formula with the following, 

When it fails to collect data from a source (assetID) for 3 consecutive days, the calculated column will change to zero for that assetID. (this does not have to be retroactive.)

Can some one help? 

 

1 ACCEPTED SOLUTION

Hey, @Chimsie

I've modified the data to reproduce your scenario. The pbix file is attached at the end.

Mesa:

a1.png

You can create a calculated column as shown below.

Result = 
COALESCE(
    [Value],
    var _id=[AssetID]
    var _date=[Date]
    var lastnoblankdate = 
    CALCULATE(
               MAX('Table'[Date]),
               FILTER(
                   ALL('Table'),
                   'Table'[AssetID]=_id&&
                   'Table'[Date]<_date&&
                   'Table'[Value]<>BLANK()
               )
    )
    var val=
    CALCULATE(
       SUM('Table'[Value]),
       FILTER(
           ALL('Table'),
           'Table'[AssetID]=_id&&
           'Table'[Date]=lastnoblankdate
       )
    )
    return
    IF(
        [Date]-lastnoblankdate<=3,
        val,
        0
    )
)

Result:

a2.png

Best regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @Chimsie 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

 

You may create a calculated column as below.

Result = 
var _asserid=[AssetID]
var _date=[Date]
var tab=
CALCULATETABLE(
    DISTINCT('Table'[Date]),
    TOPN(
        3,
        FILTER(
        'Table',
        'Table'[AssetID]=_asserid&&
        'Table'[Date]<_date&&
        'Table'[Value]<>BLANK()
        ),
        'Table'[Date]
    )
)
return
IF(
    MAXX(
        tab,
        [Date]
    )<[Date]-3,
    0,
    CALCULATE(
        MAX('Table'[Value]),
        FILTER(
            'Table',
            'Table'[AssetID]=_asserid&&
            'Table'[Date] in tab
        )
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-alq-msft 


I don't think I was completely clear.

When for an AssetID the Value is 0/blank the result should be the LASTNONBLANK, when the Value for an AssetID is 0/blank for 3 days in a row the result should be 0 as from the fourth day.

See below example:

 
 

Capture.PNG

Hey, @Chimsie

I've modified the data to reproduce your scenario. The pbix file is attached at the end.

Mesa:

a1.png

You can create a calculated column as shown below.

Result = 
COALESCE(
    [Value],
    var _id=[AssetID]
    var _date=[Date]
    var lastnoblankdate = 
    CALCULATE(
               MAX('Table'[Date]),
               FILTER(
                   ALL('Table'),
                   'Table'[AssetID]=_id&&
                   'Table'[Date]<_date&&
                   'Table'[Value]<>BLANK()
               )
    )
    var val=
    CALCULATE(
       SUM('Table'[Value]),
       FILTER(
           ALL('Table'),
           'Table'[AssetID]=_id&&
           'Table'[Date]=lastnoblankdate
       )
    )
    return
    IF(
        [Date]-lastnoblankdate<=3,
        val,
        0
    )
)

Result:

a2.png

Best regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

View solution in original post

Great, thank you very much! 🤗

Chimsie
Helper II
Helper II

Nobody who can help me with the above challenge, is it possible at all?


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.