Forum Discussion

Aurora-BI's avatar
Aurora-BI
Icon for Helper I rankHelper I
2 years ago
Solved

How to get a rolling average but exclude blank dates

Hi All,

 

I have seen a few posts on here dealing with rolling averages, but none have quite helped me acheive what I am trying to do.

 

I have a table named "Date Group Test" with 2 columns: dates and a value

 

but when I try to use: 

20 Day Rolling Average =
VAR NumDays = 20
VAR RollingSum =
CALCULATE(
    SUM('Date Group Test'[Date Count]),
DATESINPERIOD('Date Group Test'[Date Value], LASTDATE('Date Group Test'[Date Value]), -NumDays, DAY)
)
RETURN
RollingSum/NumDays
 
it returns an average number that includes dates that are not in my data set, and gives them a value of 0.
 
My current 20 day rolling average is 55 according to this formula

 

 

But when I do the calculation myself the number should be about 86.

 

Furthermore, these are the last 20 days according the the formula:

 

but the reality is, this is 13 values and 7 blank values. 

 

Does anyone know how I can filter this data so it does not insert date values I do not have with blank data? 

 

There are no records on dates missing from the table above.

 

Any help is greatly appreciated!

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    The answer should be 78.  Write these measures

    DC = SUM(Data[Date Count])
    DV = SUM(Data[Date Value])
    Measure = AVERAGEX(TOPN(20,Data,[DV],DESC),[DC])

     

7 Replies

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    2 years ago

    hi Aurora-BI ,

     

    kindly reference : Calculate Growth From Previous Non Consecutive Date (youtube.com) . 

    here instead of growth, you can subsititute the measure.

    Since this is over non consecutive days, you may not need to filter. 

     

    Regardless, i would use the below to filter if required:

    Filter( table, table[columnname] <> blank()  

     

    if this doesn't resolve the issue, kindly attach a usable sample input and output masking sensitive information

  • Aurora-BI's avatar
    Aurora-BI
    Icon for Helper I rankHelper I
    2 years ago

    adudani 

     

    Thank you for the help. 

     

    I was able to follow this video guide but am still not gettign the result I need. In my formula, I cannot seem to "find" the column that has the values I need to work against, where as in the video you provided the user was able to access his "[Total Sales]" column without issue.

     

     

    All of the data in my Power BI report comes from a dataverse a table or a company server... I am not sure how I could make a sample file with the data for others to play around without the security being an issue. There is no data that needs to be private, so if you know how I could make a sample copy I will do so.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Shouldn't the answer be 84.84?  Also, share some dummy data to work with.

     

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        You may either simply just paste the data here or share the download link.  Also, please answer the question i asked in my earlier message.