My datasource looks like following
Case_ID identify a unique business case for that the status and amount changes in time. A new record for a particular date exist only if either status or amount for the Case_ID changes. The requirement is to create visuals (table and chart) that show the total per status and day like following:
I have found several blogs how to handle semi additive measures but non handling a similar case.
Can anyone help how to define the DAX formula?
I try to describe the problem a bit further:
New record to the source data is added if and only if there is an update either for Amount or Status_ID for the Case_ID.
For example for the Case_ID = 1 was created 1.9. with Status_ID = 0 and Amount = 300. Then it was updated on 5.9. to Status_ID = 2 and Amount = 300 and finally 6.9. to Status_ID = 3, Amount = 300 (unchanged).
This means that on 1.-4.9. the Status_ID was 0 and Amount = 300, 5.9. it was Status_ID = 2, Amount=320 and 6.9. until the end of the calendar it was Status_ID = 3, Amount = 320.
The best measure I could find is following:
Last Amount = VAR lastKnownAmount = CALCULATETABLE ( SUMMARIZE ( History, History[Case_ID], History[Status_ID], "LastKnownAmount", LASTNONBLANKVALUE ( 'Date'[Date], SUM ( History[Amount] ) ) ), FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN SUMX ( lastKnownAmount, [LastKnownAmount] )
It finds the last amount for all combinations Case_ID and Status_ID and fill it to all the dates till the end of the calendar. The output filtered for Case_ID = looks following:
But I cannot get rid of the red crossed figures.
I was able to do some progress on this topic. I have added a new calculated column "Next date" to the datasource that contains the date of the next record for the same Case_ID or last date of the calendar if there is no next record:
NextDate = VAR lastKnownDate = MAX ( 'Date'[Date] ) VAR next = CALCULATE ( FIRSTNONBLANK ( 'History'[Date], 1 ), FILTER ( History, ( 'History'[Date] > EARLIER ( History[Date] ) && ( 'History'[Case_ID] = EARLIER ( 'History'[Case_ID] ) ) ) ) ) RETURN IF ( ISBLANK ( next ), lastKnownDate, next )
Now each record is valid for the range >= Date and <NextDate. I struggle in constructing the measure.
Based on your datasource table and expected table, I cannot find the relationship or calculation logic between them, could you give us the calculation logic of output value? So that we can give you specific measure. I also find an article about Semi-Additive Measures in DAX
maybe you can try the measure with this structure
Total=calculate(sum(table[Amount]),Filter(All(table),table[Status_ID]=min(table[Status_ID])&&[your date period]))
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
thank you for your feedback. I have updated the question with better explanation of the problem. I do not understand the logic in your DAX code. I have updated it to my data - the source table is "History" and the calendar table is "Date" like following:
Total = CALCULATE ( SUM ( History[Amount] ), FILTER ( ALL ( history ), history[Status_ID] = MIN ( history[Status_ID] ) && [Date] ) )
Unfortunately the result does not give much sense. If I make a date/Status_ID table filtered for Case_ID = 1 as in the question the output looks like following:
Try to use Matrix visual and set it as below:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos🙏!!
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.