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

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Share your actual Excel or .pbix file.  Also, how would you like to depict the data?  What do you want to drag to rows/columns?