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...
  • 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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft 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

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
    Microsoft 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

    • MMCBRAYER's avatar
      MMCBRAYER
      Regular Visitor
      8 years ago

      v-jiascu-msft - here is the raw data for the graph above, and the formula for DOI, i did try to create a measure but i still can't get the formula right. thanks for the help, appreciate it!

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        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