none
Creating and average statistic that updates when new tables are added Options RRS feed

  • Question

  • Hello, I have spent quite a few hours over the last month trying to find a solution to this, but I either couldn't find / understand the answers I found. My knowledge of DAX is pretty simple. 

     

    I want compare this month's sales, with the average of the previous months' sales. I have already created this with 

     

    (AVERAGE(table[column]) + AVERAGE(table[column]) + AVERAGE(table[column]))/3 etc  

     

    But this won't really work long term because a new sales table is created for each month, so the formula will just get ridiculously long, and will need to constantly be rewritten (we are comparing the historical averages for several statistics not just sales).

     

    Is there a way I can have Power BI automatically pick up and include a new table into the average when it's added at the end of the month? In Python I would use wildcards so name the past tables with a common word EG APRIL2018PAST, but this doesn't seem to be possible in Power BI.

     

    Help much appreciated.

    Thursday, June 7, 2018 4:13 PM

Answers

  • You should unpivot your data in the query editor. This will return one column with dates and one column with the amounts.

    Then in the data model, you can use a quick measure for running totals or rolling averages and adjust it to your use case if necessary.

    I'd recommend to the dedicated Power BI community forum for further questions: https://community.powerbi.com/t5/Forums/ct-p/Forums


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by asd1425215 Thursday, June 7, 2018 8:22 PM
    Thursday, June 7, 2018 6:38 PM
    Moderator