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 =
"LastKnownAmount", LASTNONBLANKVALUE ( 'Date'[Date], SUM ( History[Amount] ) )
FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] )
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:
VAR lastKnownDate =
MAX ( 'Date'[Date] )
VAR next =
FIRSTNONBLANK ( 'History'[Date], 1 ),
'History'[Date] > EARLIER ( History[Date] )
&& ( 'History'[Case_ID] = EARLIER ( 'History'[Case_ID] ) )
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
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:
SUM ( History[Amount] ),
ALL ( history ),
history[Status_ID] = MIN ( history[Status_ID] )
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: