Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
Best Regards!
Dale
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
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
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
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 - 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!
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
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |