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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.