Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chimsie
Helper III
Helper III

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.

Great, thank you very much! 🤗

Chimsie
Helper III
Helper III

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.