Generate the rolling average excluding the blank cells

[cristian-ciia]

Hello everyone,

I am building a financial dashboard with prices of stocks. I have a Dim_Calendar_Table that I use as main calendar. On the other hand, I have other table with Prices, Dates and Names. 

Well, I am trying to get the rolling std deviation but I have a problem, I have weekend days int the Dim_calendar_Table and I don't know how to ignore them in order to calculate the last 4 days for example.

4_Day_Rolling_Price = 
VAR DateFilter=
    DATESINPERIOD(
        'Dim_Calendar DAX'[Date],
        MAX('Dim_Calendar DAX'[Date]),
        -4,DAY)
VAR RollingAvg = 
    CALCULATE(
        [Precio],
        DateFilter)
RETURN
    DIVIDE(
        RollingAvg, 
        COUNTROWS(
            FILTER(
                DateFilter,
                    [Precio]<>BLANK()


)))

I will appreciate your help!

Many thanks! 

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below provides some ideas on how to create a solution for your dataset.

Four days rolling avg excluding blank: =
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _beforethancurrentdate =
    ADDCOLUMNS (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ),
        "@price", [Price measure:]
    )
VAR _filterblank =
    SUMMARIZE (
        FILTER ( _beforethancurrentdate, [@price] <> BLANK () ),
        'Calendar'[Date]
    )
VAR _top4table =
    TOPN ( 4, _filterblank, 'Calendar'[Date], DESC )
RETURN
    CALCULATE ( [Price measure:], _top4table ) / COUNTROWS ( _top4table )