cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jirim
Frequent Visitor

DAX Measure - Status and value change in time

Hello Experts

 

My datasource looks like following

Case_IDDateStatus_IDAmount
101.09.20210300
105.09.20213320
106.09.20213320
205.09.20213100
302.09.20210200
307.09.20212200

 

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:

 0123
01.09.2021300   
02.09.2021500   
03.09.2021500   
04.09.2021500   
05.09.2021200 320200
06.09.2021200  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:

xxdkbl8BGi.png

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.

4 REPLIES 4
v-xiaotang
Community Support
Community Support

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.

jirim
Frequent Visitor

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:
yca1EYuliI.png

jirim
Frequent Visitor

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.

VahidDM
Community Champion
Community Champion

Hi @jirim 

 

Try to use Matrix visual and set it as below:

 

VahidDM_0-1632614363452.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors