Forum Discussion

lazuri's avatar
lazuri
New Member
8 years ago
Solved

Creating and average statistic that updates when new tables are added

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.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi lazuri,

     

    I assume that all data tables share the same structure in each monthly dataset. You can store all monthly files in a folder, then, load data into desktop via Folder connector. For details, please see 

    Combining Excel Files hosted on a SharePoint folder with Power BI

    Load Multiple Excel (xls or xlsx) Files

     

    This way, data from all months are stored in a single dataset. Suppose it looks like below. If there is no MonthNo column in original source, you can add a calculated column: MonthNo = Test2[Date].[MonthNo]

     

    Then, create measures:

    TM sales = SUM(Test2[Sales])

Average in last 3 month =
CALCULATE (
    SUM ( Test2[Sales] ),
    FILTER (
        ALL ( Test2 ),
        Test2[MonthNo] <= MAX ( Test2[MonthNo] )
            && Test2[MonthNo]
                >= MAX ( Test2[MonthNo] ) - 2
    )
)
    / 3

     

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi lazuri,

     

    I assume that all data tables share the same structure in each monthly dataset. You can store all monthly files in a folder, then, load data into desktop via Folder connector. For details, please see 

    Combining Excel Files hosted on a SharePoint folder with Power BI

    Load Multiple Excel (xls or xlsx) Files

     

    This way, data from all months are stored in a single dataset. Suppose it looks like below. If there is no MonthNo column in original source, you can add a calculated column: MonthNo = Test2[Date].[MonthNo]

     

    Then, create measures:

    TM sales = SUM(Test2[Sales])

Average in last 3 month =
CALCULATE (
    SUM ( Test2[Sales] ),
    FILTER (
        ALL ( Test2 ),
        Test2[MonthNo] <= MAX ( Test2[MonthNo] )
            && Test2[MonthNo]
                >= MAX ( Test2[MonthNo] ) - 2
    )
)
    / 3

     

    Best regards,

    Yuliana Gu