How to Calculate Daily Average with a Separate Working Days Table?

[Anonymous]

Hi,

I've tried a few measures myslef and from this forum to calculate daily average with specific working days per month. They all failed.

I have this separate working days table:

Date	Working Days
1/1/2018	22
2/1/2018	20
3/1/2018	22
4/1/2018	21
5/1/2018	22
6/1/2018	21
7/1/2018	21
8/1/2018	23
9/1/2018	19
10/1/2018	23
11/1/2018	20
12/1/2018	18
1/1/2019	22
2/1/2019	20
3/1/2019	21
4/1/2019	22
5/1/2019	22
6/1/2019	20
7/1/2019	22
8/1/2019	22
9/1/2019	20
10/1/2019	23
11/1/2019	19
12/1/2019	19
1/1/2020	22
2/1/2020	20
3/1/2020	22
4/1/2020	22
5/1/2020	20
6/1/2020	22
7/1/2020	22
8/1/2020	21
9/1/2020	21
10/1/2020	22
11/1/2020	19
12/1/2020	20

I have another table with volume, the simplfied version is:

Calendar month	Calendar Year	Customer	Revenue	Volume RPT	Date
Jan	2019	Csutomer 1	580	3	1/1/2019
Jan	2019	Csutomer 2	12789.07	70	1/1/2019
Feb	2019	Csutomer 3	2089	19	2/1/2019
Feb	2019	Csutomer 4	8799.02	76	2/1/2019
Mar	2019	Csutomer 5	7724.5	70	3/1/2019
Mar	2019	Csutomer 6	4468	40	3/1/2019
Mar	2019	Csutomer 7	7941.07	70	3/1/2019
Apr	2019	Csutomer 8	2491	23	4/1/2019
May	2019	Csutomer 9	397.02	2	5/1/2019
Jun	2019	Csutomer 10	23751.12	181	6/1/2019
Jul	2019	Csutomer 11	1389	13	7/1/2019
Jul	2020	Csutomer 12	29572.06	117	7/1/2020
Jul	2020	Csutomer 13	427	2	7/1/2020
Jul	2020	Csutomer 14	403	2	7/1/2020
Aug	2020	Csutomer 15	41292.12	377	8/1/2020
Aug	2020	Csutomer 16	3810.54	31.5	8/1/2020
Aug	2020	Csutomer 17	1143.01	9	8/1/2020
Aug	2020	Csutomer 18	3390.5	30.5	8/1/2020
Aug	2020	Csutomer 19	1810	16.5	8/1/2020

*The two date columns are connected in table relationship.

How do I get a measure to calculate daily volume and revenue?

Thank you in advance for any help!

Regards, 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

These should get you there.

Revenue Portion = SUMX ( Volume, DIVIDE ( Volume[Revenue] , RELATED ( Days[Working Days] ) ) )

Volume Portion = SUMX ( Volume, DIVIDE ( Volume[Volume RPT] , RELATED ( Days[Working Days] ) ) )

I have attached my sample file for you to take a look at.

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[Anonymous]

Hi jdbuchanan71,

It works!! Big thank you to you!

Regards,