Conditional Index

[OlafA69]

Dear all,

I'm happy to be a new user in this forum. I face an issue and I couldn't found a solution for this problem yet. I have the following problem. I want to create a Burn-Down Chart based on the Planned and the Actual columns. In column C, I add a simple Index which I sort in a descending order afterwards. But in column D2, I want to start with the same number as in column C2 and count down the number only when the State is set to 'Closed'. If it is not set to 'Closed' then the value should be taken from the previous cell. I have found a similar solution in this forum but not in combination with the index values in column C. I would be happy if you could give me a hint to get this problem solved. Thank you very much in advance for your support.

Thanks

Olaf

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Actual CC =
VAR _startdate =
    MIN ( Data[Date] )
VAR _startnumber =
    MAX ( Data[Planned] )
VAR _conditiontable =
    ADDCOLUMNS ( Data, "@condition", IF ( Data[State] = "Closed", -1 ) )
VAR _cumulatecondition =
    SUMX (
        FILTER ( _conditiontable, Data[Date] <= EARLIER ( Data[Date] ) ),
        [@condition]
    )
RETURN
    _startnumber + _cumulatecondition

[OlafA69]

Hello Forum,

in my first post I got great support from Jihwan_Kim and SparteBI. This time I would like to add two more filters. So this time I would like to count down the number in column D when the state is both "Closed" or "Done" or "Cancelled". I tried to solve it with an OR condition but it didn't work out. Any help is appreciated.

Thanks

Olaf

[SpartaBI · Accepted solution]

@OlafA69 this is another alternative, but @Jihwan_Kim solution is also great.

Actual CC = 
VAR _startnumber =
    MAX ( Data[Planned] )
VAR _minus_count =
    COUNTROWS(
        FILTER(
            Data, 
            Data[Planned] >= EARLIER(Data[Planned]) && Data[State] = "Closed")
    )
VAR _result = _startnumber - _minus_count
RETURN
    _result

      

Showcase Report – Contoso By SpartaBI

[OlafA69]

Hello SpartaBI,

thanks a lot for your feedback. It works frine on my end. It looks such a simple code but I would never have been able to write it with my limited skills in DAX language. One question. When I would like to add more than one option than the state 'Closed', e.g. 'Cancelled' and 'Done' how can I add these two additional states to the script?

Thanks

Olaf

[SpartaBI]

@OlafA69 no worries, you will get there 🙂
Not sure what exactly did you mean, but if you will write the exact requirment whenever you need it (you don't have to now), just open a new question and refer to this one and mention me. 

Please don't forget to accept the previous message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Actual CC =
VAR _startdate =
    MIN ( Data[Date] )
VAR _startnumber =
    MAX ( Data[Planned] )
VAR _conditiontable =
    ADDCOLUMNS ( Data, "@condition", IF ( Data[State] = "Closed", -1 ) )
VAR _cumulatecondition =
    SUMX (
        FILTER ( _conditiontable, Data[Date] <= EARLIER ( Data[Date] ) ),
        [@condition]
    )
RETURN
    _startnumber + _cumulatecondition