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.
Good afternoon,
I'm not directly asking for a solution because it may be too complex. But I'm asking for advice/tips on how you may approach the following hurdle I'm facing.
Hurdle: Image #1 {Sample Month) below shows the % of Sales per Business Days of the month. This is calculated each month. Imagine #2 {Multiple Months} combines each month & calculates the average amongst months. Re-worded another way, calculates the average monthly cumulative % of sales for each business day.
For example: If it was January 15th, it may be the 10th business day. I would want the average of '% of cumulative sales' for the 10th business day in the whole time period.
How would you best find the cumulative business day of the month, and return the average cumulative % of sales for past months?
Do you have a calendar table that defines the business days for each month? If so you would simply need to create a cummumaltive amount measure. something like:
Total To Day = Calculate(sum('table'[amount]), filter(all(date, date[monthnum] >= min(date[monthnum]) && date[monthnum] <= max(date[monthnum]) && date[bus_day] <= max(date{bus_day]))
Total Month = Calculate(sum('table'[amount]), filter(all(date, date[monthnum] >= min(date[monthnum]) && date[monthnum] = max(date[monthnum]))
% of Sales = Divide([Total To Day], [Total Month])
I think this should work for all your scenarios. if you can provide a pbix file then i could make the formulas for exact and ensure they work
Proud to be a Super User!
Hi,
Sounds like an interesting one. Share the link from where i can download your data.
PARALLELPERIOD?
https://msdn.microsoft.com/en-us/library/ee634873.aspx
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |