March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I have a sales table that has invoice date and a sales result. I also have a date dimension table as below. Working day is a flag that has a value of 0 for weekends and public holidays and a value of 1 for all other days. Trading day number is a number the counts up each work day and then starts again for each month. What I am trying to do is set up a table visual that is filterd by the user selecting a month and displays the trading day number, the sales for current year and the sales for last year. The tricky part is some months have more trading days and others and that can be the case when comparing for example March 2024 to March 2023.
A sample of the calendar table is below where I have March 2024 and March 2023
Hi @AC77 ,
I did not understand your question very well, so if you do not mind, could you explain to me in more detail the trading day and how you expected the results to be obtained. This will allow me to better understand and address your question.
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ada, Thankyou for responding. Trading Day is a number the resets back to 1 each month and counts up each working day. A working day is a 0 or 1 value where 0 is any non working day such as a weekend or public holiday. Trading day counts up each day working day = 1. I have provided a example below using March 2024 and March 2023. The source sales data is the forst reply i posted and the expected output i need is the 2nd reply
Date | Day of Week | Month | Year | Working Day | Trading Day No |
1/03/2023 | Wed | March | 2023 | 1 | 1 |
2/03/2023 | Thu | March | 2023 | 1 | 2 |
3/03/2023 | Fri | March | 2023 | 1 | 3 |
4/03/2023 | Sat | March | 2023 | 0 | 3 |
5/03/2023 | Sun | March | 2023 | 0 | 3 |
6/03/2023 | Mon | March | 2023 | 1 | 4 |
7/03/2023 | Tue | March | 2023 | 1 | 5 |
8/03/2023 | Wed | March | 2023 | 1 | 6 |
9/03/2023 | Thu | March | 2023 | 1 | 7 |
10/03/2023 | Fri | March | 2023 | 1 | 8 |
11/03/2023 | Sat | March | 2023 | 0 | 8 |
12/03/2023 | Sun | March | 2023 | 0 | 8 |
13/03/2023 | Mon | March | 2023 | 1 | 9 |
14/03/2023 | Tue | March | 2023 | 1 | 10 |
15/03/2023 | Wed | March | 2023 | 1 | 11 |
16/03/2023 | Thu | March | 2023 | 1 | 12 |
17/03/2023 | Fri | March | 2023 | 1 | 13 |
18/03/2023 | Sat | March | 2023 | 0 | 13 |
19/03/2023 | Sun | March | 2023 | 0 | 13 |
20/03/2023 | Mon | March | 2023 | 1 | 14 |
21/03/2023 | Tue | March | 2023 | 1 | 15 |
22/03/2023 | Wed | March | 2023 | 1 | 16 |
23/03/2023 | Thu | March | 2023 | 1 | 17 |
24/03/2023 | Fri | March | 2023 | 1 | 18 |
25/03/2023 | Sat | March | 2023 | 0 | 18 |
26/03/2023 | Sun | March | 2023 | 0 | 18 |
27/03/2023 | Mon | March | 2023 | 1 | 19 |
28/03/2023 | Tue | March | 2023 | 1 | 20 |
29/03/2023 | Wed | March | 2023 | 1 | 21 |
30/03/2023 | Thu | March | 2023 | 1 | 22 |
31/03/2023 | Fri | March | 2023 | 1 | 23 |
1/03/2024 | Fri | March | 2024 | 1 | 1 |
2/03/2024 | Sat | March | 2024 | 0 | 1 |
3/03/2024 | Sun | March | 2024 | 0 | 1 |
4/03/2024 | Mon | March | 2024 | 1 | 2 |
5/03/2024 | Tue | March | 2024 | 1 | 3 |
6/03/2024 | Wed | March | 2024 | 1 | 4 |
7/03/2024 | Thu | March | 2024 | 1 | 5 |
8/03/2024 | Fri | March | 2024 | 1 | 6 |
9/03/2024 | Sat | March | 2024 | 0 | 6 |
10/03/2024 | Sun | March | 2024 | 0 | 6 |
11/03/2024 | Mon | March | 2024 | 1 | 7 |
12/03/2024 | Tue | March | 2024 | 1 | 8 |
13/03/2024 | Wed | March | 2024 | 1 | 9 |
14/03/2024 | Thu | March | 2024 | 1 | 10 |
15/03/2024 | Fri | March | 2024 | 1 | 11 |
16/03/2024 | Sat | March | 2024 | 0 | 11 |
17/03/2024 | Sun | March | 2024 | 0 | 11 |
18/03/2024 | Mon | March | 2024 | 1 | 12 |
19/03/2024 | Tue | March | 2024 | 1 | 13 |
20/03/2024 | Wed | March | 2024 | 1 | 14 |
21/03/2024 | Thu | March | 2024 | 1 | 15 |
22/03/2024 | Fri | March | 2024 | 1 | 16 |
23/03/2024 | Sat | March | 2024 | 0 | 16 |
24/03/2024 | Sun | March | 2024 | 0 | 16 |
25/03/2024 | Mon | March | 2024 | 1 | 17 |
26/03/2024 | Tue | March | 2024 | 1 | 18 |
27/03/2024 | Wed | March | 2024 | 1 | 19 |
28/03/2024 | Thu | March | 2024 | 1 | 20 |
29/03/2024 | Fri | March | 2024 | 0 | 20 |
30/03/2024 | Sat | March | 2024 | 0 | 20 |
31/03/2024 | Sun | March | 2024 | 0 | 20 |
Hi @AC77 ,
I understand. You can put data into a Matrix to achieve your output:
And you can off the Column Subtotals here:
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thankyou, while that method works it doesn't suit the manner is which users will be working with our data. We need a last year measure as a requirement from stakeholders is the user will select a month (in this case March 2024) to base the output on
I have tried the following dax on the last year measure:
Trading Day No | Sales Last Year | Sales |
1 | 90 | 140 |
2 | 64 | 66 |
3 | 172 | 95 |
4 | 72 | 51 |
5 | 81 | 82 |
6 | 64 | 200 |
7 | 56 | 90 |
8 | 183 | 97 |
9 | 90 | 78 |
10 | 60 | 79 |
11 | 86 | 179 |
12 | 33 | 73 |
13 | 177 | 28 |
14 | 90 | 66 |
15 | 48 | 93 |
16 | 35 | 129 |
17 | 98 | 31 |
18 | 127 | 57 |
19 | 22 | 62 |
20 | 22 | 245 |
Total | 22 | 1941 |
expected output
Trading Day | 2023 | 2024 |
1 | 90 | 140 |
2 | 64 | 66 |
3 | 172 | 95 |
4 | 72 | 51 |
5 | 81 | 82 |
6 | 64 | 200 |
7 | 56 | 90 |
8 | 183 | 97 |
9 | 90 | 78 |
10 | 60 | 79 |
11 | 86 | 179 |
12 | 33 | 73 |
13 | 177 | 28 |
14 | 90 | 66 |
15 | 48 | 93 |
16 | 35 | 129 |
17 | 98 | 31 |
18 | 127 | 57 |
19 | 22 | 62 |
20 | 22 | 245 |
21 | 91 | |
22 | 42 | |
23 | 34 | |
Total | 1837 | 1941 |
sample of sales data
Date | Sales |
1/03/2023 | 90 |
2/03/2023 | 64 |
3/03/2023 | 62 |
4/03/2023 | 45 |
5/03/2023 | 65 |
6/03/2023 | 72 |
7/03/2023 | 81 |
8/03/2023 | 64 |
9/03/2023 | 56 |
10/03/2023 | 72 |
11/03/2023 | 44 |
12/03/2023 | 67 |
13/03/2023 | 90 |
14/03/2023 | 60 |
15/03/2023 | 86 |
16/03/2023 | 33 |
17/03/2023 | 32 |
18/03/2023 | 54 |
19/03/2023 | 91 |
20/03/2023 | 90 |
21/03/2023 | 48 |
22/03/2023 | 35 |
23/03/2023 | 98 |
24/03/2023 | 23 |
25/03/2023 | 52 |
26/03/2023 | 52 |
27/03/2023 | 22 |
28/03/2023 | 22 |
29/03/2023 | 91 |
30/03/2023 | 42 |
31/03/2023 | 34 |
1/03/2024 | 68 |
2/03/2024 | 28 |
3/03/2024 | 44 |
4/03/2024 | 66 |
5/03/2024 | 95 |
6/03/2024 | 51 |
7/03/2024 | 82 |
8/03/2024 | 65 |
9/03/2024 | 38 |
10/03/2024 | 97 |
11/03/2024 | 90 |
12/03/2024 | 97 |
13/03/2024 | 78 |
14/03/2024 | 79 |
15/03/2024 | 33 |
16/03/2024 | 67 |
17/03/2024 | 79 |
18/03/2024 | 73 |
19/03/2024 | 28 |
20/03/2024 | 66 |
21/03/2024 | 93 |
22/03/2024 | 68 |
23/03/2024 | 33 |
24/03/2024 | 28 |
25/03/2024 | 31 |
26/03/2024 | 57 |
27/03/2024 | 62 |
28/03/2024 | 73 |
29/03/2024 | 71 |
30/03/2024 | 42 |
31/03/2024 | 59 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |