How to calculate average volume where I specify number of working days

[kiltannen]

I am having difficulty figuring out how to approach this question

I have some data where a volume each day is generated at up to 20 locations (Originations)

I have successfully presented this with the originations in the rows and the total for the month in columns in a matrix. 

(Not hard or complicated at all - this just presents the starting place)

I then want a measure that gives me the average per day each month in the next column - BUT I want to specify the number of working days in the month to use for the calculation.

I thought - no worries, I'll add another table that gives me 1 day each month for each origination - and a value for the Number of Working Days.

Problem is I cannot link my data_sample to the DIM_Wkg_Days. It says I must have my field unique in one of the tables.

The ultimate aim - is to be able to project how many working days in that month next year - multiplied by the avg volume this year - to give me the avg volume next year allowing for a different number of working days in each month from year to year. And then multiply that avg volume by the Num of Wkg Days next year in that month - to give me a projected volume in that month next year for the specific origination row.  We are also applying a multiplier for growth in volume. (hopefully this makes sense)

Here is a link to a sample pbix that hopefully helps make my question a little bit clearer:

DATA_Sample

It is very possible that my actual approach is flawed, and I need to take a different tack, so any suggestions are appreciated.

I suspect I may need some kind of link table - but I can't even figure out what it needs to look like.  I am very open to suggestions of doing it a different way - but if possible I would like it to be done in PowerBI rather than Excel - We have a method in Excel right now but it means a fair amount of work and you have all the problems of formula's gettting broken and different copies of files with different versions and not having one source of truth.

We are building out our reporting suite using PowerBI and SSRS as our delivery tool at this point, and staying with that at the presentation layer would be better.

[v-yuezhe-msft · Accepted solution]

@kiltannen,

Create the following column in dateLookup table.

isworkday = IF(WEEKDAY(dateLookup[date])>0 && WEEKDAY (dateLookup[date])<6,1,0)

Create the measure using dax below in dateLookup table.
WorkingDay = CALCULATE(SUM(dateLookup[isworkday]))

At last, create the AvgVol measure in data_sample table.

AvgVol = [TotalVolume]/[WorkingDay]




Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@kiltannen,

Create the following column in dateLookup table.

isworkday = IF(WEEKDAY(dateLookup[date])>0 && WEEKDAY (dateLookup[date])<6,1,0)

Create the measure using dax below in dateLookup table.
WorkingDay = CALCULATE(SUM(dateLookup[isworkday]))

At last, create the AvgVol measure in data_sample table.

AvgVol = [TotalVolume]/[WorkingDay]




Regards,
Lydia

[kiltannen · Accepted solution]

Hi @v-yuezhe-msft - Thank you for the suggestion here.

I suspect I was not perfectly clear - the reason I have muliple dates in my date lookup is that my originating locations each have one of 2 months of the year when they have a diferent number of working days.  This is because of regional anniversary days.  This would be like having an org in the USA spanning multiple states, where each state has it's own Public holidays and so has to be handled independantly.

The end result of this is that my date lookup ended up with multiple rows for every date. This is why I am unable to link my date lookup with my data table.   Without trying it out - when I read your suggestion, I'm not sure how it overcomes that problem.

In the end - I used my data source query (MS SQL) to give each Data row the number of working days this month and the number of working days next year for the same month. I did this by storing in my dateDimension the number of working days for each location, for that date.

This correctly seperates the originating locations and how many working days they have each month.  

A different approach I contemplated was to run a separate query in Power BI for each originating location to identify a list of dates and # of working days for each location independantly.   Then join all those to the data, that seemed less likely of success, in that it looked like a lot more effort to manage all those queries so I did not test or pursue it.