Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate consecutive backlogs over a specific threshold value.

I have a sample data table with given Columns A, B and E. Need to calculate Columns C and D as outputs in Power BI and use Col D in Histogram. Giving a Cyclic reference error as it needs to calculate using a previous value from same column. I have tried using Lookupvalue but still giving cyclic error. Thanks for your kind help -

 

 

  • smpa01's avatar
    smpa01
    3 years ago

    Anonymous  you can write a measure like this

    Measure =
CALCULATE (
    [dropPlusRollOver],
    OFFSET ( -1, DISTINCT ( ALL ( 'fact' ) ), ORDERBY ( 'fact'[Date], ASC ) )
)
    + MAX ( 'fact'[Drop] )

     

    PFA

5 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous  provide a sample data that here that can be copied over to PBI. Power BI at current capacity is unable to extract data from a pic.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
      DateDropDrop Plus RolloverLeft from Previous DayCapacity
      1/1/20229500  10000
      1/2/20221011210112010000
      1/3/20228500861211210000
      1/4/202286008600010000
      1/5/20221112211122010000
      1/6/20221200013122112210000
      1/7/2022998013102312210000
      1/8/2022850011602310210000
      1/9/202274009002160210000
      1/10/202262336233010000
      1/11/20221234412344010000
      1/12/2022999912343234410000
      1/13/20221122313566234310000
      1/14/2022887712443356610000
      1/15/2022850010943244310000
      1/16/20224500544394310000
      1/17/20221322213222010000
      1/18/2022850011722322210000
      1/19/2022942211144172210000
      1/20/2022888810032114410000
      1/21/2022777778093210000
  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous  you can write a measure like this

    Measure =
CALCULATE (
    [dropPlusRollOver],
    OFFSET ( -1, DISTINCT ( ALL ( 'fact' ) ), ORDERBY ( 'fact'[Date], ASC ) )
)
    + MAX ( 'fact'[Drop] )

     

    PFA

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for the response but Column C and Column D have to be output using Column A and Column B and a capacity parameter. i think you used them as input for measures. 

      Also i need to sho highlighted Column D in a histogram visual.