How to show LASTNONBLANK Date from LASTDAST

[bajimmy1983]

Hi all, how are you? 

Maybe this DAX is simple, but I've tried lot's of thing and failed. Until get here I usually really try hard! Do you know how to accomplish this? Could you help? I don't want to do it adding Custom Columns in dataset. I immagine it can be done using Measures instead.

So, I have a simple fTable like below and I just want to calculate Last Week based on fTable given dates (not contiguous date) . I can do it hard coding the numbers of days inside DATEADD because I know people are feeding it every Monday, but I want to replace "-7" inside DATEADD for an automatic number of days in the past. So, DAX has to be intelligent enough to know how many days it has to go back. Even if I know people updates data every Monday I want to grant that when someone forgets, DAX will use the correct number to go back. 

eg: If I didn't have 21/10/2019... So, DAX has to know last day before 28/10/2019 is 14/10/2019 and then go back 14 days instead of just 7 giving us a "Custom Last Week". 

Measure: 

DAX LW = 
IF (
    [Total de Horas Tipo de Saldo] <> BLANK ();
    CALCULATE ( [Total de Horas Tipo de Saldo]; DATEADD ( 'Date'[Date]; -7; DAY ) )
)

Table Visual: 

DESIRED OUTCOME

Thanks a lot to everyone in advance and have a nice week! 🙂 

[Ashish_Mathur]

Hi,

Share the link from where I can download your PBI file.  In your file, ensure that there is no data for October 21.

[bajimmy1983]

Hi @Ashish_Mathur , thanks for replying 🙂

Please, you can access at: https://1drv.ms/u/s!Aq2_y0xKTrAvg4wpaKOeUZmnBG4VmQ?e=o0E7l3

Best,

Jimmy

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

Previous date on which hours were booked = if(ISBLANK([Total de Horas]),BLANK(),CALCULATE(LASTNONBLANK('Date'[Date],[Total de Horas]),DATESBETWEEN('Date'[Date],MINX(ALL('Date'[Date]),'Date'[Date]),MAX('Date'[Date])-1)))

Measure = if(ISBLANK([Previous date on which hours were booked]),BLANK(),CALCULATE([Total de Horas],DATESBETWEEN('Date'[Date],[Previous date on which hours were booked],[Previous date on which hours were booked])))

Hope this helps.

[bajimmy1983]

@Ashish_Mathur, hi again! 

It worked pretty well. I just "broke" your two measures into pieces (VARs) in order to understand the flow and then adapted it to mine. Now I got it and can see entire scenario! 😍😎

Total de Horas Tipo de Saldo LW = 
VAR _TestLASTNONBLANK =
    LASTNONBLANK ( 'Date'[Date]; [Total de Horas Tipo de Saldo] )
VAR _TestMINX =
    MINX ( ALL ( 'Date'[Date] ); 'Date'[Date] )
VAR _TestMAX =
    MAX ( 'Date'[Date] ) - 1
VAR _PreviousDate =
    IF (
        ISBLANK ( [Total de Horas Tipo de Saldo] );
        BLANK ();
        CALCULATE (
            LASTNONBLANK ( 'Date'[Date]; [Total de Horas Tipo de Saldo] );
            DATESBETWEEN (
                'Date'[Date];
                MINX ( ALL ( 'Date'[Date] ); 'Date'[Date] );
                MAX ( 'Date'[Date] ) - 1
            )
        )
    )
RETURN
    IF (
        _PreviousDate <> BLANK ();
        CALCULATE (
            [Total de Horas Tipo de Saldo];
            DATESBETWEEN ( 'Date'[Date]; _PreviousDate; _PreviousDate )
        )
    )

Thank you so much once again.

[Ashish_Mathur]

You are welcome.

[bajimmy1983]

That’s great. I’ll apply it ASAP and let you know.

Thanks again so much for your super support!