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

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
Icon for Microsoft Employee rankMicrosoft 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