Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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