Forum Discussion

HumanX's avatar
HumanX
Frequent Visitor
2 years ago

Circular Cumulative

Hi I getting hard time to analyze this formula


Pays = average of the data
Cumulative pays  = typical formula for cumulative
Dials = another data

cumulative Dials = Getting hard time to figure

To break the formula for cumulative dials
(([previous Data of cumulative dials] * [previous Data of Cumulative Pays]) + ([current data of dials] * [Current Data of Pays]))/Current data of Cumulative Pays

D234=((.679733*11)+(71% or .71*7))/18

Could anyone help me with this because I can see it as circular because the column I'm trying to solve is the one needed also??? So its so confusing. 


Note: the first value for dials also the first value for cumulative dials so the formula will be applied on the next.

Thank you! 

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
    If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi HumanX ,

    You can create a measure as below to get it, please find the details in the attachment.

    New_Culmulative Dials = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _preday =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _date )
    )
VAR _preculpays =
    SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= _preday ), [Pays] )
VAR _preculdials =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _preday ),
        [Cumulative Dials]
    )
RETURN
    (
        _preculdials * _preculpays
            + ( SUM ( 'Table'[Dials] ) * SUM ( 'Table'[Pays] ) )
    ) / [Culmulative Pays]

    Best Regards

    • HumanX's avatar
      HumanX
      Frequent Visitor
      2 years ago

      Hi Anonymous 

      This solution is way closer as I almost got the computation for that. Is there a way you can help me if the Cumulative Dials Column's  data has only 1 value? 

      We able to calculate the New_cumulative Dials because Cumulative Dials has data

      VAR _preculdials = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _preday ), [Cumulative Dials]

      What if the column for Cumulative Dials is only .67? Can we able to solve the remaining in New_cumulative  Dials? .68 and .67000.


      Note: I'm trying to configure that if I can get the first data in Dials Column which is .67 and put it to my Cumulative Dials and try to process the measure again.

      Thank you! 

      • HumanX's avatar
        HumanX
        Frequent Visitor
        2 years ago

        For more clarity im adding new example data


        Pays56711
        Cumulative Pays5111829
        Dials67%69%71%73%
        Cumulative Dials67%?????????

         

        Here in the table shows that i just copied the first value for Dials for cumulative Dials. Then for the succeding values, we should use the output for cumulative Dials to get the next value of it


        Hope it supports my question above. Thank you!