cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VisheshVats
Helper I
Helper I

Get last value in date column

Hello,

I need to perform ISBLANK on a date column where there are blank values, so i thought to change the null values to 01/01/1000.
But, i cant still use IF function to compare the MAX[DATE]-1 = 01/01/1000, as MAX[DATE]-1 returns the actual max date -1 .
For instance if  max[DATE] = 08/08/2022, then MAX[DATE]-1 will be 07/08/2022 whereas i want it to return 01/01/1000.

Also is there a way to check for nulls in date column?
Thanks in advance.

 

Last Date.PNG

1 ACCEPTED SOLUTION

Hi @VisheshVats 

go to power query, add an index column, then use the column code below

 

Column = 
var _last=MAXX(FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Column1]<>BLANK()),[Index])
var _date=MAXX(FILTER('Table','Table'[Index]=_last),[Column1])
return 
IF(ISBLANK('Table'[Column1]),_date-'Table'[Index]+_last,'Table'[Column1])

 

result

OliT_0-1660206614519.png

 

 

Regards,

OliT

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@VisheshVats , Not very clear, Try like

 

coalesce([Date], date(1000,1,1)

 

But I doubt power bi support year below 1899

In simple words

I wish this measure to return the value 1;

 

Measure 2 = IF(ISBLANK(MAX(CUF[Date])-1),1,0)
 

as the date 07/08/2022 is blank in my date column;

 

Capture.PNG

Hi @VisheshVats 

go to power query, add an index column, then use the column code below

 

Column = 
var _last=MAXX(FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Column1]<>BLANK()),[Index])
var _date=MAXX(FILTER('Table','Table'[Index]=_last),[Column1])
return 
IF(ISBLANK('Table'[Column1]),_date-'Table'[Index]+_last,'Table'[Column1])

 

result

OliT_0-1660206614519.png

 

 

Regards,

OliT

Thanks for the response, but it doesnt solve my problem.

I'll try to explain again. I wish find NAV-CHAESH for MAX date and divide it by the NAV-CHAESH of the previous date and subtract 1 so that i get the percentage difference. This works fine if there's no gap in the dates, but in my case there are some 'null' values. What i wish to achieve is that if the [DATE]-1 is 'null', then look for the next nonblank value.

 

My measure for this:

 

Measure =
 IFERROR(DIVIDE(CALCULATE(SUM(CUF[NAV-CHAESH]),FILTER(ALL(CUF[Date]),CUF[Date]=max(CUF[Date]))),
    IF(ISBLANK(MAX(CUF[Date])-1),
    LASTNONBLANKVALUE(CALCULATETABLE(ALLSELECTED(CUF[Date]),CUF[Date]<TODAY()-1),SUM(CUF[NAV-CHAESH])),
    CALCULATE(sum(CUF[NAV-CHAESH]), FILTER(ALL(CUF[Date]),CUF[Date]=max(cuf[Date])-1))),"NA")-1,"NA")

  

Data set:

 

Capture.PNG

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.