Need help with DAX Measure in Power BI - Returning Last Available Date

[Youcef_Data]

Hello Power BI Community,

I'm currently working on a project where I'm facing an issue with a DAX measure, and I'm hoping someone here can assist me.

Problem:
  I have a table (Table1) with dates and corresponding items. I want to create a DAX measure that returns the "last available date less than selected date" for a selected item if the selected date doesn't exist in the table.
My issue arises when I select a date that doesn't exist; it returns a blank value.

Script: Here's the DAX script I've come up with:

" measeure =
VAR SelectedDate = MAX(Table1[DATE])
VAR SelectedKeyItem = SELECTEDVALUE(Table1[ID_ITEMS]) -- Calculate the last available date less than SelectedDate for the same item
VAR LastAvailableDate =
    CALCULATE( MAX(Table1[DATE]),
        FILTER( ALL(Table1),
             Table1[DATE] < SelectedDate &&
             Table1[ID_ITEMS] = SelectedKeyItem ) )
     -- Check if the selected date exists in the table
  VAR DateExists = COUNTROWS( FILTER( ALL(Table1),
                           Table1[DATE] = SelectedDate &&
                           Table1[ID_ITEMS] = SelectedKeyItem ) )
-- If the selected date doesn't exist, return the last available date -- Otherwise, return SelectedDate
     VAR Result = IF(
                        DateExists = 0,
                        LastAvailableDate, SelectedDate )
      RETURN
          Result
"

Sample Data:

ID_ITEMS	DATE	Moved_Qty	Running Total	INDEX
1	1/1/2015	1	1	1
1	1/2/2015	2	3	2
2	1/3/2015	10	10	1
2	1/4/2015	5	15	2
1	1/5/2015	5	8	3
1	1/5/2015	-3	5	4
3	1/7/2015	3	3	1
2	1/9/2015	-7	8	3
2	1/11/2015	-2	6	4
3	1/11/2015	-6	-3	2

Thank you in advance for your help!

Best regards.

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result clearly.

[amitchandak]

@Youcef_Data ,

Last ID Date = var _max = maxx(filter(ALLSELECTED(Data), Data[ID_ITEM] = Max(Data[ID_ITEM])), Data[Date])
return
CALCULATE(Sum(Data[Moved_Qty]), filter((Data) , Data[Date] =_max))

Also refer, for correct sum refer 2nd blog

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Youcef_Data]

@amitchandak

Thanks for your response,
but your measure  still return Blank value 
"

measeure 2 =
  var _max = maxx(filter(ALLSELECTED(Table1), Table1[ID_ITEMS] = Max(Table1[ID_ITEMS])), Table1[DATE])
returnCALCULATE(Sum(Table1[Moved_Qty]), filter((Table1) , Table1[DATE] =_max))"

let me explain the issue
 when I select a ID_ITEMS as filter (for example ID_ITEMS 2 ) and I select a date in power bi "don't exist in the table " ( for example  1/6/2015) 
the measure return Blank value ( it should return 1/4/2015 as last date available less than selectde date for ID_ITEMS 2 ).

[v-xinruzhu-msft · Accepted solution]

Hi @Youcef_Data 

Thanks for the solution @amitchandak  provided, and i want to offer some infotmation for you to refer to.

You can create a calendar table.

Calendar = CALENDAR(DATE(2015,1,1),DATE(2015,12,31))

Then create a relationship between tables.

Then create a measure.

Measure = CALCULATE(MAX(Table1[DATE]),ALLEXCEPT('Table1',Table1[ID_ITEMS]),Table1[DATE]<=MAX('Calendar'[Date]))

Last, put the date column of the calendar to the slicer.

Output

Best Regards!

Yolo Zhu

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

[Youcef_Data]

Thanks @v-xinruzhu-msft , it's works.