Forum Discussion

MMCBRAYER's avatar
MMCBRAYER
Regular Visitor
8 years ago
Solved

6 months moving average

Hi,

 

I'm working on a project to move all excel files to POWER BI, i'm new to this and I can say that I'm pretty much excited to solved this challenge. I tried searching through forums but no luck and i'm close to the wall now, and would appreciate the help of the community here;

 

I have created a column to SUM the inventory QTY per Month and a column to show MAX of Inventory QTY based on the column SUM of Inventory QTY per Month, now I'm not familiar how to incorporate the moving formula below to a table.

 

 

 

 

 

 

 

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

    Hi MMCBRAYER,

     

    Please check the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgSdlT8WMv_5-jYIQ.

    Because I don't know your model structure, I tried like this. If you want more help, please provide your model.

     

    Result =
SUM ( Inventory[inventory usd] )
    / CALCULATE (
        SUM ( 'Sell Out'[sell out usd] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -6, MONTH ),
        ALL ( 'Calendar'[YearMonth] )
    )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

8 Replies

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

    Hi MMCBRAYER,

     

    If you want to move all the Excel files to Power BI, all the calculations in the excel should be discarded. That means only import all the source data into Power BI. And then do all the calculations, such as monthly sales, in the Power BI.

     

    In your scenario, a Date table is needed. A simple one could be like this:

    Calendar =
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )

    Create a measure. 

    lastSixMonthSales =
CALCULATE (
    SUM ( Sales[Quantity] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -6, MONTH )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    If you want more details, please post a sample in TEXT mode. The PBIX file would be great.

     

    Best Regards!

    Dale

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

      Hi MMCBRAYER,

       

      Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

       

      Best Regards!
      Dale

    • MMCBRAYER's avatar
      MMCBRAYER
      Regular Visitor
      8 years ago

      Hi v-jiascu-msft,

       

      Thank you for your inputs, it is possible to add a calculated column with this formula "DOI = QTY/6mos AVG of Sales*30", i'm still trying to figure this one out :(

       

      here is the sample graph, the line bar will represent the DOI.

       

       

      Thank you in advance!

      Mike

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

        MMCBRAYER,

         

        Hi Mike,

         

        I think the DOI should be a measure rather than a calculated column. Could you please post a sample in TEXT mode? The dummy PBIX would be great. I can't write a formula without data and its structure.

         

        Best Regards!

        Dale

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

    Hi MMCBRAYER,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale