Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MMCBRAYER
Regular Visitor

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.

6 month moving average.PNG

 

6 month moving average2.PNG

 

 

 

 

 

 

1 ACCEPTED SOLUTION

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] )
    )

6 months moving average2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

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

6 months moving average.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Capture.PNG

 

Thank you in advance!

Mike

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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!

 

1.PNG

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] )
    )

6 months moving average2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.