Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I have a data table with 3 colums: DataDay, UnitsSold, ExpiryDate.
I would like to calculate cumulative amount for the units sold, but exclude from the calculation where above ExpiryDate < DataDay, so I would like to see 8 instead of 11 for the DataDay of June 10, since June 9 is an earlier date than June 10.
It is very important that someone can help me.
Thank you very much!!!
Solved! Go to Solution.
Hi,
Please open the file that I have attached in my last reply.
And in my opnion, I think your visualizatoin is using Date from Data table, but Date has to be from Calendar table.
Just in case, I am attaching the file again.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a measure and a visualization.
I hope the below can provide some ideas on how to create a solution for your datamodel.
Expected result measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _expirydate =
SUMMARIZE (
FILTER ( ALL ( Data ), Data[ExpiryDate] >= _currentdate ),
'Calendar'[Date]
)
RETURN
CALCULATE (
SUM ( Data[UnitSold] ),
WINDOW ( 1, ABS, 0, REL, _expirydate, ORDERBY ( 'Calendar'[Date], ASC ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan_Kim, unfortunately I can't attach a .pbix file, but I'm trying to show you that I failed:
Unfortunately, the DAX formula included the 10 volumes of June 7 (expiry date June 8 ) and the 10 volumes of June 8 ( expiry date June 8 ) in the sum of June 9, although it should not have done so, because the rule is to exclude if expiry date < dataday...
Hi,
Could you please check the below measure, just in case if I understood your question correctly?
Expected result measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _expirydate =
FILTER ( ALL ( Data ), Data[ExpiryDate] >= _currentdate )
RETURN
IF (
NOT ISBLANK ( [Unit sold measure:] ),
SUMX ( FILTER ( _expirydate, Data[Date] <= _currentdate ), Data[UnitSold] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan_Kim, thank you very much for your reply :), unfortunately only the volume 92 of 10 June is correct, and we see this number for every day, which is not good. But for 9 June the correct value is 71, for 8 June: 67 and for 7 June: 26 is the correct.
Hi,
Please open the file that I have attached in my last reply.
And in my opnion, I think your visualizatoin is using Date from Data table, but Date has to be from Calendar table.
Just in case, I am attaching the file again.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
hi Jihwan_Kim, you are right, it works and thank you very much!!! 🙂
Hello, thank you very much for your reply :), I will let you know if it works.