Conceptual starting point for DAX measure

[Jkaelin]

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?  

 Sample Single Month

Multiple Months

[richbenmintz]

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

[Ashish_Mathur]

Hi,

Sounds like an interesting one.  Share the link from where i can download your data.

[Greg_Deckler]

PARALLELPERIOD?

https://msdn.microsoft.com/en-us/library/ee634873.aspx