Forum Discussion
How to Calculate Daily Average with a Separate Working Days Table?
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,
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.
2 Replies
- jdbuchanan71
Super User
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.