Circular Cumulative

[HumanX]

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! 

[Anonymous]

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]

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]

For more clarity im adding new example data

Pays	5	6	7	11
Cumulative Pays	5	11	18	29
Dials	67%	69%	71%	73%
Cumulative Dials	67%	???	???	???

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!

[lbendlin]

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-...
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/1447...