Hi. My first post here was a “fiasco”, I'll try again and thank you for any help.
I need to create a table with my sales target based on a daily basis, someones have proposed a formula where you divide the Target of every month by the (calendar) days of each month, but since we do not work on weekends and holidays, the total days of month are not calendar days but working days. It means that “working days of the month” are less than “calendar days of the month”.
I have a “Calendar” table in which I register: “Date”, “Days of Month”, “Working Day” among other fields. If a specific day (Date) is a working day on the “Working Day” field I register 1 and 0 if it’s not (Maybe a weekend or a holiday).
This is an example of that table with only January/2020, for this exercise:
Date | Year | Month Name | Month | Quarter | End of Quarter | Days in Month | Day of Week | Working Day | End of Month | Year-Month | Day of Month |
1/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 3 | 0 | 31/1/2020 | 2020-01 | 1 |
2/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 4 | 1 | 31/1/2020 | 2020-01 | 2 |
3/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 5 | 1 | 31/1/2020 | 2020-01 | 3 |
4/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 6 | 0 | 31/1/2020 | 2020-01 | 4 |
5/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 0 | 0 | 31/1/2020 | 2020-01 | 5 |
6/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 1 | 1 | 31/1/2020 | 2020-01 | 6 |
7/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 2 | 1 | 31/1/2020 | 2020-01 | 7 |
8/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 3 | 1 | 31/1/2020 | 2020-01 | 8 |
9/1/2020 | 2020 | enero | 1 | 1 | 31/3/2020 | 31 | 4 | 1 | 31/1/2020 | 2020-01 | 9 |
Total Days in Month = 31
Total Working days = 22 You’ll get it if you Sum = [Working Day]
Every month of every year could have different working days. I can define, automatically, if it’s working (or off) day for all may calendar table.
You can find this calendar table here: https://1drv.ms/x/s!AtgI4wlIMN89g50nk-ks92741mAvDg?e=ljTC1n
On the other hand, I have a Monthly Target by Manufacturer and Seller table with these fields: Month (It's really a End Of Month field), Manufacturer, Seller and Month Target. The table below is just an example. You can find the file here: https://1drv.ms/x/s!AtgI4wlIMN89g50qqLvxAK3c09iocg?e=iiGrfL
Month | Manufacturer | Seller | Month Target |
31/1/2020 | Proveedor C | Vendedor 1 | 690,000.00 |
31/1/2020 | Proveedor C | Vendedor 11 | 3,460,000.00 |
31/1/2020 | Proveedor C | Vendedor 13 | 2,510,000.00 |
31/1/2020 | Proveedor C | Vendedor 14 | 3,140,000.00 |
31/1/2020 | Proveedor D | Vendedor 13 | 90,000.00 |
31/1/2020 | Proveedor D | Vendedor 2 | 790,000.00 |
31/1/2020 | Proveedor D | Vendedor 4 | 790,000.00 |
Finally, I need a table with the following fields: Date (day by day), Manufacturer, Seller and a Daily Target (resulting of dividing the total Month Target by the total working days of the related month and, finally, populate the table with that daily target just on working days, registering "0" or BLANK() if it´s 0 on calendar table. All of this, with DAX formulas.
This example was made directly on Excel. Can yo imagine doing this for every month of this year, MANUALLY? For example, on Daily Target you'll see zero ("-") on January 1st, this is because we didn't work that day. You can download the sample file from here:
DateManufacturerSellerDaily Target
| 1/1/2020 | Proveedor C | Vendedor 1 | - |
| 1/1/2020 | Proveedor C | Vendedor 11 | - |
| 1/1/2020 | Proveedor C | Vendedor 13 | - |
| 1/1/2020 | Proveedor C | Vendedor 14 | - |
| 1/1/2020 | Proveedor C | Vendedor 2 | - |
| 1/1/2020 | Proveedor C | Vendedor 4 | - |
| 1/1/2020 | Proveedor C | Vendedor 6 | - |
| 1/1/2020 | Proveedor C | Vendedor 8 | - |
| 1/1/2020 | Proveedor C | Vendedor 9 | - |
Thanks a lot for your help.
