question

VincentDialloNort-5962 avatar image
0 Votes"
VincentDialloNort-5962 asked VincentDialloNort-5962 answered

DAX lastNonblankvalue not working as a measure

Hi,

I am having issue with the last blank value as I am trying to show a tab with the Date - Value - last non empty value . I use the demo sample from dax.io ( the link include my code )
https://dax.do/dD83JH5nka6NhB/


The query I use is

 define
  measure Sales[Sales Amount Last]=
 LASTNONBLANKVALUE ( 'Date'[Date], [Sales Amount] )
 EVALUATE
 CALCULATETABLE ( 
     ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "Sales Amount", [Sales Amount] ,
     "Last non blank",Sales[Sales Amount Last] 
     ),
     'Date'[Date] >= DATE ( 2007, 2, 7 ) &&
     'Date'[Date] <= DATE ( 2007, 2, 16 ) 
 )

But I have the same blank on the column Sales Amount and the column Last non blank. I was expecting the Last non blank value to show the value from the previous day when blank.
But the result is similar in both column :
134316-image.png

So I was expecting to see 15,502.99 on the column "Last non blank" for the date 2007-02-07. Any idea what I am missing ?







office-excel-itproazure-analysis-services
image.png (16.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As this is a DAX question, I'm updating the tag to direct it to the area for Power Pivot.

0 Votes 0 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered PRADEEPCHEEKATLA-MSFT edited

Hello @VincentDialloNort-5962
Thanks for the ask and using Microsoft Q&A platform .
Your ask was

So I was expecting to see 15,502.99 on the column "Last non blank" for the date 2007-02-07. Any idea what I am missing ?

I am confused I think the ask should be

So I was expecting to see 15,502.99 on the column "Last non blank" for the date 2007-02-08. Any idea what I am missing ?

If thats the case , please use the below query .

FIRSTNONBLANKVALUE and LASTNONBLANKVALUE return the first and last
-- non blank values in the selection.
DEFINE
MEASURE Sales[Sales Amount Prev] =
CALCULATE ( [Sales Amount], PREVIOUSDAY ( 'Date'[Date] ) )
MEASURE Sales[Sales Amount Prev non] =
VAR LastAmount =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] < MAX ( 'Date'[Date] ) )
)
VAR result =
CALCULATE ( [Sales Amount], 'Date'[Date] = LastAmount )
RETURN
if ([Sales Amount]=BLANK (),result,[Sales Amount])
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( 'Date'[Date] ),
"Sales Amount", [Sales Amount],
"last non blank ", Sales[Sales Amount Prev non]
),
'Date'[Date] >= DATE ( 2007, 2, 7 )
&& 'Date'[Date] <= DATE ( 2008, 2, 16 )
)
ORDER BY 'Date'[Date]



134454-image.png


Please do let me know how it goes .
Thanks
Himanshu


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

    • Want a reminder to come back and check responses? Here is how to subscribe to a notification

    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


image.png (7.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VincentDialloNort-5962 avatar image
0 Votes"
VincentDialloNort-5962 answered

Thanks a lot

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.