Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello Experts
My datasource looks like following
Case_ID | Date | Status_ID | Amount |
1 | 01.09.2021 | 0 | 300 |
1 | 05.09.2021 | 3 | 320 |
1 | 06.09.2021 | 3 | 320 |
2 | 05.09.2021 | 3 | 100 |
3 | 02.09.2021 | 0 | 200 |
3 | 07.09.2021 | 2 | 200 |
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:
0 | 1 | 2 | 3 | |
01.09.2021 | 300 | |||
02.09.2021 | 500 | |||
03.09.2021 | 500 | |||
04.09.2021 | 500 | |||
05.09.2021 | 200 | 320 | 200 | |
06.09.2021 | 200 | 420 | ||
07.09.2021 | 200 | 420 | ||
08.09.2021 | 200 | 420 | ||
... | 200 | 420 |
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?
EDIT:
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.
EDIT 2:
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.
Hi @jirim
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
https://www.sqlbi.com/articles/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]))
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xiaotang,
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:
Hi @VahidDM
my question was probably not clear. I do not know how to build the messure in DAX that would give the results as in the sample output table.
Hi @jirim
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🙏!!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |