Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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.
Solved! Go to 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.
Hi,
Based on the data that you have shared, show the expected result clearly.
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
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 ).
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.
User | Count |
---|---|
90 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |