Forum Discussion

jirim's avatar
jirim
Frequent Visitor
5 years ago

DAX Measure - Status and value change in time

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....
Need Help
v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
jirim
Frequent Visitor
5 years ago

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: