Forum Discussion

apanta's avatar
apanta
Icon for Helper I rankHelper I
2 years ago
Solved

LASTNONBLANK DAX

Hi All,
I have the below table from where I want to get the last non-blank value for a particular patient in the last 1 year when the date slicer was used.

 

For example: If I select the month as December, I want to get the result as zero for this PatientID 13189 since the latest value is 0 for November (5/11/2023) but it should show me the result as 3 when October is selected for the month slicer since the latest date would be 19/9/2023. The formula is working fine up to some extent but the trouble is when I select the month November/December, instead of zero, it gives me the result as 3 because the formula is treating zero as a blank value and is jumping to the number three. Could someone please help me with this?

The formulas that I am using are:

1st:
Restr Score = IF(CALCULATE(sum('Restrictive'[Restr.])) = BLANK(), 0, CALCULATE(SUMX('Restrictive','Restrictive'[Restr.]), FILTER('Restrictive','Restrictive'[DateCreated] = 'Restrictive'[MaxDate])))


2nd:

Result =
Var reference = MAX(Date_dim[Date])
var prevdate = DATE ( YEAR ( Reference) - 1, MONTH ( Reference)-1, DAY ( Reference) )
var result =
if('Restrictive (2)'[Restr Score] <> 0, 'Restrictive (2)'[Restr Score],calculate(LASTNONBLANK('Restrictive (2)'[Restr.],'Restrictive (2)'[Restr Score]),REMOVEFILTERS(Date_dim),'Restrictive (2)'[DateCreated] >= prevdate  && 'Restrictive (2)'[DateCreated] <=reference))
return result


Many thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi apanta 

     

    I have some understanding of your question:

    Your DAX statement will recognize 0 as a blank value.

    I used your data to do some testing:

    Then create a measure:

    result =
var Correctresult = CALCULATE(
        SUM('Table'[Restr.]),
        FILTER(
            'Table',
            'Table'[DataCreated] = MAX('Table'[DataCreated])
        )
    )
var Blankif = ISBLANK(MAX('Table'[Restr.]) )
RETURN IF(Blankif=FALSE(),Correctresult)

    Use ISBLANK() to determine if the value is null or not.

    The result is as followed:

     

    Best Regards,

    Zhengdong Xu

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

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create 2 slicers - year and Month name from the Calendar Table and select 1 year & 1 month.  To your matrix visual, drag Customer name.  Write these measures:

    R = sum('Restrictive'[Restr.])

    Last known R = calculate([r],calculatetable(lastnonblank(Calendar[Date],calculate([r])),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))))

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create 2 slicers - year and Month name from the Calendar Table and select 1 year & 1 month.  To your matrix visual, drag Customer name.  Write these measures:

    R = sum('Restrictive'[Restr.])

    Last known R = calculate([r],calculatetable(lastnonblank(Calendar[Date],calculate([r])),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi apanta 

     

    I have some understanding of your question:

    Your DAX statement will recognize 0 as a blank value.

    I used your data to do some testing:

    Then create a measure:

    result =
var Correctresult = CALCULATE(
        SUM('Table'[Restr.]),
        FILTER(
            'Table',
            'Table'[DataCreated] = MAX('Table'[DataCreated])
        )
    )
var Blankif = ISBLANK(MAX('Table'[Restr.]) )
RETURN IF(Blankif=FALSE(),Correctresult)

    Use ISBLANK() to determine if the value is null or not.

    The result is as followed:

     

    Best Regards,

    Zhengdong Xu

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

    • apanta's avatar
      apanta
      Icon for Helper I rankHelper I
      2 years ago

      Thank you so much for your reply!

      This formula seems to be working but my only concern is I have to check for the lastnonblank value in the last 12 months only. E.g: in my data example here, if the slicer is selected as November 2024, it should look for the lastnonblank value in the last 1 year (i.e. till November 2023 only).