cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kiltannen
Frequent Visitor

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

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)

Data_Sample-001.png

 

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.

Data_Sample-002.png

 

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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]

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kiltannen
Frequent Visitor

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

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.

View solution in original post

2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

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

@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]

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kiltannen
Frequent Visitor

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

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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors