Forum Discussion

ToKo's avatar
ToKo
Frequent Visitor
6 years ago
Solved

Cumulative line values for chart

Hello,

 

I would like to build cumulative line value for  below chart.

 

I have plan for all month for example its 2400min in December I have 18 working days so 2400/18 = 133,33 daily.

I would like to build line that show me cumulative at the next days so 1 day will be 133,33 2 day will be 266,66 etc.

How can I do this ? 🙂 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI ToKo ,

    Did your table contain the original amount field? I'd like to suggest you use the following measure formula with original amount fields:

    Measure = 
VAR currDate =
    MAX ( 'Table'[Date] )
VAR dateRange =
    CALCULATETABLE (
        VALUES ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR total =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR duration =
    DATEDIFF ( MINX ( dateRange, [Date] ), MAXX ( dateRange, [Date] ), DAY )
RETURN
    total / duration 
        * DATEDIFF ( MINX ( dateRange, [Date] ), currDate, DAY )

    Regards,

    Xiaoxin Sheng

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    ToKo there are many ways to achieve this, it is important to know how your data looks like to provide the appropriate solution. Can you share sample data here? 

  • ToKo's avatar
    ToKo
    Frequent Visitor
    6 years ago

    parry2k  I have one table with cumulative sum for each line and AvrageForLine for this i would like to build cumulative avrage line for chart.

     

    Line DateCumulativeSumAvrageForLine
    11112019-12-013428
    11122019-12-015044
    11132019-12-017065
    11112019-12-025028
    11122019-12-028044
    11132019-12-0210065
    11112019-12-038028
    11122019-12-0312044
    11132019-12-0315065
    11112019-12-0411028
    11122019-12-0416044
    11132019-12-0419065
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI ToKo ,

      Did your table contain the original amount field? I'd like to suggest you use the following measure formula with original amount fields:

      Measure = 
VAR currDate =
    MAX ( 'Table'[Date] )
VAR dateRange =
    CALCULATETABLE (
        VALUES ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR total =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )
VAR duration =
    DATEDIFF ( MINX ( dateRange, [Date] ), MAXX ( dateRange, [Date] ), DAY )
RETURN
    total / duration 
        * DATEDIFF ( MINX ( dateRange, [Date] ), currDate, DAY )

      Regards,

      Xiaoxin Sheng