Sum of Moving Averages for a Card Visual

[diegocprado]

Hi, I'm using the Moving Average as a Goal for my Entrances data for a association program. I also need it to be dynamically filtered by Source/Medium (facebook, google, email...), that the user select in the Dashboard.

I'm using the following code to calculate this Moving Average, and it's working nice in Time Series Visuals:

MM Entrances = 

//Selecting the date in the range
VAR _LastDate =
    MAX ( 'Date'[Date] )

//Defining the duration to be considered for average calcualtion
VAR _Duration = [Duration Calculation]

//Filtering the Calendar Table for the defined range
VAR _CalculationPeriod =
    FILTER (
        ALL ( 'Date'[Date] ),                            -- Removing any external filter context applied
        AND (
            'Date'[Date] > _LastDate - _Duration,  -- Calculate the range start date
            'Date'[Date] <= _LastDate              -- Calcualte the range end date
        )
    )

//Calculating the Moving Average
VAR _MovingAverage =
    
        CALCULATE (
            AVERAGEX('Date',[Total Entrances]),  --Calculating average of the total value
            _CalculationPeriod,
            ALLEXCEPT('Funnel','Funnel'[Source/ Medium])
        )
    
RETURN
    _MovingAverage

My Graphic is all-right with this: 

But i need it to also be in a Card Visual, and it's showing the Moving Average for the Last Date.

I need to get the SUM of all Moving Averages for the selected Period in order to make it work.

My thoughts right now are: I should have a virtual table to summarize the filtered data for Entrances, by date, in a column, and then calculate the Moving Average by day in another column. So i can SUM the Moving Averages and show it in a card.

I have failed in all my atempts to do so. And it may be a easier way to do it.. Can you please help my and my team the way out of this problem? 

[v-eqin-msft]

Hi @diegocprado ,

I think 20048 is the sum of all value in 27/08/2021-30/08/2021. But without some data sample, I could not clarify your scenario. You could try to use ISINSCOPE() to change the total value.

Or provide some data about your tables or share me with your pbix file after removing sensitive data.

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

[Fowmy]

@diegocprado 

Can you try the following measure which is a modification to yours?

MM Entrances =
VAR _LastDate =
    MAX ( 'Date'[Date] )
VAR _Duration = [Duration Calculation]
VAR _CalculationPeriod =
    DATESINPERIOD ( 'Date'[Date], _LastDate, - _Duration, MONTH )
VAR _MovingAverage =
    CALCULATE (
        AVERAGEX ( VALUES ('Date'[Date] ), [Total Entrances] ),
        _CalculationPeriod,
        ALLEXCEPT ( 'Funnel', 'Funnel'[Source/ Medium] )
    )
RETURN
    _MovingAverage

[diegocprado]

It made the same thing as my previous one..

I've almost got a solution here: 

Medida = 

//Selecting the date in the range
VAR _LastDate =
    MAX ( 'Date'[Date] )

//Defining the duration to be considered for average calcualtion
VAR _Duration = [Days to MM]

//Filtering the Calendar Table for the defined range
VAR _CalculationPeriod =
    FILTER (
        ALL ( 'Date'[Date] ),                            -- Removing any external filter context applied
        AND (
            'Date'[Date] > _LastDate - _Duration,  -- Calculate the range start date
            'Date'[Date] <= _LastDate              -- Calcualte the range end date
        )
    )

//Calculating the Moving Average
VAR _MovingAverage =
    
        CALCULATE (
            AVERAGEX('Date',[Total NU]),  --Calculating average of the total value
            _CalculationPeriod,
            ALLEXCEPT('Funnel', 'Funnel'[Source/Medium])
        )

return

sumx(
    SUMMARIZE(
        'Funnel',
        'Funnel'[Date],
        "MM NU", 
        _MovingAverage),
        [MM NU])
       

But for some reason, the sum of the Data from de Measure is diferent from the real number (if you sum it manually, offline).
 It should SUM less than 20.000