Average fill in

[Anonymous]

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. 

Date	Customer_ID	Value					Date	Customer_ID	Value
1-1-2021	1	1					1-1-2021	1	1
1-4-2021	1	4					1-2-2021	1	2
1-10-2021	1	10					1-3-2021	1	3
1-3-2021	2	3					1-4-2021	1	4
1-4-2021	2	4					1-5-2021	1	5
1-5-2021	2	6					1-6-2021	1	6
1-7-2021	2	7					1-7-2021	1	7
1-9-2021	2	5					1-8-2021	1	8
1-10-2021	2	8					1-9-2021	1	9
1-3-2021	3	2					1-10-2021	1	10
1-4-2021	3	2					1-3-2021	2	3
1-5-2021	3	5					1-4-2021	2	4
1-7-2021	3	2					1-5-2021	2	6
1-8-2021	3	4					1-6-2021	2	6,5
							1-7-2021	2	7
							1-8-2021	2	6
							1-9-2021	2	5
							1-10-2021	2	8
							1-3-2021	3	2
							1-4-2021	3	2
							1-5-2021	3	5
							1-6-2021	3	3,5
							1-7-2021	3	2
							1-8-2021	3	4
							1-9-2021	3	4
							1-10-2021	3	4

[v-rzhou-msft · Accepted solution]

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]

This is great @v-rzhou-msft !! 

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 ;)).