Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average fill in

Hey everyone, 

 

I have a customer table which returns a date, customer_ID and Value only on the days the customer is active. I want to have a calculated column/measure which returns the average between those days. So, for Customer_ID 1, the result for 2-1-2021 is 2, and for the next day 3, because on January 1st, the value was 1 and on the 4th, the value was 4. 

And secondly, the last day Customer_ID was 1-8-2021, with the value 4. That value should also be given for the next days till today (in this case 1-10-2021). And that value should change if the customer will be active again, when the real value can be measured, and the average between those days will change too. 

 

I think this is quite complex, so I hope someone could help me out with this. 

 

DateCustomer_IDValue    DateCustomer_IDValue
1-1-202111    1-1-202111
1-4-202114    1-2-202112
1-10-2021110    1-3-202113
1-3-202123    1-4-202114
1-4-202124    1-5-202115
1-5-202126    1-6-202116
1-7-202127    1-7-202117
1-9-202125    1-8-202118
1-10-202128    1-9-202119
1-3-202132    1-10-2021110
1-4-202132    1-3-202123
1-5-202135    1-4-202124
1-7-202132    1-5-202126
1-8-202134    1-6-202126,5
       1-7-202127
       1-8-202126
       1-9-202125
       1-10-202128
       1-3-202132
       1-4-202132
       1-5-202135
       1-6-202133,5
       1-7-202132
       1-8-202134
       1-9-202134
       1-10-202134
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Try this code to achieve your goal.

     

    Table 2 = 
VAR _Date =
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
VAR _ID =
    VALUES ( 'Table'[Customer_ID] )
VAR _Generate =
    GENERATE ( _ID, _Date )
VAR _Filter =
    SUMMARIZE (
        FILTER (
            _Generate,
            MINX (
                FILTER ( 'Table', 'Table'[Customer_ID] = EARLIER ( [Customer_ID] ) ),
                'Table'[Date]
            ) <= [Date]
                && MAXX (
                    FILTER ( 'Table', 'Table'[Customer_ID] = EARLIER ( [Customer_ID] ) ),
                    'Table'[Date]
                ) >= [Date]
        ),
        [Customer_ID],
        [Date]
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _Filter,
        "Value",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Customer_ID] = EARLIER ( [Customer_ID] )
                        && 'Table'[Date] = EARLIER ( [Date] )
                )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Value1",
            VAR _MAXDATE_Before =
                MAXX (
                    FILTER (
                        _ADD1,
                        [Value] <> BLANK ()
                            && [Date] < EARLIER ( [Date] )
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Date]
                )
            VAR _MINDATE_After =
                MINX (
                    FILTER (
                        _ADD1,
                        [Value] <> BLANK ()
                            && [Date] > EARLIER ( [Date] )
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Date]
                )
            VAR _DATEDIFF =
                DATEDIFF ( _MAXDATE_Before, _MINDATE_After, DAY )
            VAR _VALUE_Before =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Date] = _MAXDATE_Before
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Value]
                )
            VAR _VALUE_After =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Date] = _MINDATE_After
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Value]
                )
            VAR _DAYDIFF2 =
                DATEDIFF ( _MAXDATE_Before, [Date], DAY )
            RETURN
                IF (
                    [Value] <> BLANK (),
                    [Value],
                    _VALUE_Before
                        + DIVIDE ( _VALUE_After - _VALUE_Before, _DATEDIFF ) * _DAYDIFF2
                )
    )
RETURN
    SUMMARIZE ( _ADD2, [Customer_ID], [Date], [Value1] )

     

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    Try this code to achieve your goal.

     

    Table 2 = 
VAR _Date =
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
VAR _ID =
    VALUES ( 'Table'[Customer_ID] )
VAR _Generate =
    GENERATE ( _ID, _Date )
VAR _Filter =
    SUMMARIZE (
        FILTER (
            _Generate,
            MINX (
                FILTER ( 'Table', 'Table'[Customer_ID] = EARLIER ( [Customer_ID] ) ),
                'Table'[Date]
            ) <= [Date]
                && MAXX (
                    FILTER ( 'Table', 'Table'[Customer_ID] = EARLIER ( [Customer_ID] ) ),
                    'Table'[Date]
                ) >= [Date]
        ),
        [Customer_ID],
        [Date]
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _Filter,
        "Value",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Customer_ID] = EARLIER ( [Customer_ID] )
                        && 'Table'[Date] = EARLIER ( [Date] )
                )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Value1",
            VAR _MAXDATE_Before =
                MAXX (
                    FILTER (
                        _ADD1,
                        [Value] <> BLANK ()
                            && [Date] < EARLIER ( [Date] )
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Date]
                )
            VAR _MINDATE_After =
                MINX (
                    FILTER (
                        _ADD1,
                        [Value] <> BLANK ()
                            && [Date] > EARLIER ( [Date] )
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Date]
                )
            VAR _DATEDIFF =
                DATEDIFF ( _MAXDATE_Before, _MINDATE_After, DAY )
            VAR _VALUE_Before =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Date] = _MAXDATE_Before
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Value]
                )
            VAR _VALUE_After =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Date] = _MINDATE_After
                            && [Customer_ID] = EARLIER ( [Customer_ID] )
                    ),
                    [Value]
                )
            VAR _DAYDIFF2 =
                DATEDIFF ( _MAXDATE_Before, [Date], DAY )
            RETURN
                IF (
                    [Value] <> BLANK (),
                    [Value],
                    _VALUE_Before
                        + DIVIDE ( _VALUE_After - _VALUE_Before, _DATEDIFF ) * _DAYDIFF2
                )
    )
RETURN
    SUMMARIZE ( _ADD2, [Customer_ID], [Date], [Value1] )

     

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is great Anonymous !! 

      Power BI was not able to calculate the formula, cause it was it had to store too much capacity for the calculation. Nevertheless, I really appreciate what you did and I am sure that I will use this formula in the future (for a less bigger file ;)).