Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Index

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Anonymous 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

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

Anonymous 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      SpartaBI
      Community Champion
      4 years ago

      Anonymous 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 🙂