Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AC77
Frequent Visitor

Last Year Sales measure based on Trading Day rather than date

‎06-06-2024 11:05 PM

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





Labels:
Message 1 of 8
186 Views
0
7 REPLIES 7
Anonymous
Not applicable

‎06-09-2024 10:29 PM

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.

Message 4 of 8
142 Views
0
AC77
Frequent Visitor
In response to Anonymous

‎06-10-2024 04:08 PM

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

DateDay of WeekMonthYearWorking DayTrading Day No
1/03/2023WedMarch202311
2/03/2023ThuMarch202312
3/03/2023FriMarch202313
4/03/2023SatMarch202303
5/03/2023SunMarch202303
6/03/2023MonMarch202314
7/03/2023TueMarch202315
8/03/2023WedMarch202316
9/03/2023ThuMarch202317
10/03/2023FriMarch202318
11/03/2023SatMarch202308
12/03/2023SunMarch202308
13/03/2023MonMarch202319
14/03/2023TueMarch2023110
15/03/2023WedMarch2023111
16/03/2023ThuMarch2023112
17/03/2023FriMarch2023113
18/03/2023SatMarch2023013
19/03/2023SunMarch2023013
20/03/2023MonMarch2023114
21/03/2023TueMarch2023115
22/03/2023WedMarch2023116
23/03/2023ThuMarch2023117
24/03/2023FriMarch2023118
25/03/2023SatMarch2023018
26/03/2023SunMarch2023018
27/03/2023MonMarch2023119
28/03/2023TueMarch2023120
29/03/2023WedMarch2023121
30/03/2023ThuMarch2023122
31/03/2023FriMarch2023123
1/03/2024FriMarch202411
2/03/2024SatMarch202401
3/03/2024SunMarch202401
4/03/2024MonMarch202412
5/03/2024TueMarch202413
6/03/2024WedMarch202414
7/03/2024ThuMarch202415
8/03/2024FriMarch202416
9/03/2024SatMarch202406
10/03/2024SunMarch202406
11/03/2024MonMarch202417
12/03/2024TueMarch202418
13/03/2024WedMarch202419
14/03/2024ThuMarch2024110
15/03/2024FriMarch2024111
16/03/2024SatMarch2024011
17/03/2024SunMarch2024011
18/03/2024MonMarch2024112
19/03/2024TueMarch2024113
20/03/2024WedMarch2024114
21/03/2024ThuMarch2024115
22/03/2024FriMarch2024116
23/03/2024SatMarch2024016
24/03/2024SunMarch2024016
25/03/2024MonMarch2024117
26/03/2024TueMarch2024118
27/03/2024WedMarch2024119
28/03/2024ThuMarch2024120
29/03/2024FriMarch2024020
30/03/2024SatMarch2024020
31/03/2024SunMarch2024020




Message 5 of 8
112 Views
0
Anonymous
Not applicable
In response to AC77

‎06-10-2024 07:13 PM

Hi @AC77 ,

I understand. You can put data into a Matrix to achieve your output:

vyifanwmsft_0-1718071585641.png

And you can off the Column Subtotals here:

vyifanwmsft_1-1718071654700.png

Final output:

vyifanwmsft_2-1718071924941.png

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.

Last Year Sales measure based on Trading Day rather than date.pbix
Message 6 of 8
105 Views
0
AC77
Frequent Visitor
In response to Anonymous

‎06-10-2024 09:03 PM

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 

Message 7 of 8
99 Views
0
AC77
Frequent Visitor
In response to AC77

‎06-10-2024 09:14 PM

I have tried the following dax on the last year measure:

Sales Last Year = CALCULATE (
    SUM (SALES_TABLE[SALES]),
    FILTER (
        ALL (D_DATE),
        D_DATE[TRADING_DAY_NO] = MAX (D_DATE[TRADING_DAY_NO] )
            &&  D_DATE[YEAR_ID] = MAX(D_DATE[YEAR_ID] ) - 1
            &&  D_DATE[MONTH_NUMBER] = MAX(D_DATE[MONTH_NUMBER])
    )
)


but i get the below  where it matches up the sales to last year correctly on trading day though does not display the last 3 days for last year and for the total is displays the last value displayed

Trading Day NoSales Last YearSales
190140
26466
317295
47251
58182
664200
75690
818397
99078
106079
1186179
123373
1317728
149066
154893
1635129
179831
1812757
192262
2022245
Total221941
Message 8 of 8
94 Views
0
AC77
Frequent Visitor

‎06-06-2024 11:21 PM

expected output

Trading Day20232024
190140
26466
317295
47251
58182
664200
75690
818397
99078
106079
1186179
123373
1317728
149066
154893
1635129
179831
1812757
192262
2022245
2191 
2242 
2334 
Total18371941
Message 3 of 8
175 Views
0
AC77
Frequent Visitor

‎06-06-2024 11:19 PM

sample of sales data

DateSales
1/03/202390
2/03/202364
3/03/202362
4/03/202345
5/03/202365
6/03/202372
7/03/202381
8/03/202364
9/03/202356
10/03/202372
11/03/202344
12/03/202367
13/03/202390
14/03/202360
15/03/202386
16/03/202333
17/03/202332
18/03/202354
19/03/202391
20/03/202390
21/03/202348
22/03/202335
23/03/202398
24/03/202323
25/03/202352
26/03/202352
27/03/202322
28/03/202322
29/03/202391
30/03/202342
31/03/202334
1/03/202468
2/03/202428
3/03/202444
4/03/202466
5/03/202495
6/03/202451
7/03/202482
8/03/202465
9/03/202438
10/03/202497
11/03/202490
12/03/202497
13/03/202478
14/03/202479
15/03/202433
16/03/202467
17/03/202479
18/03/202473
19/03/202428
20/03/202466
21/03/202493
22/03/202468
23/03/202433
24/03/202428
25/03/202431
26/03/202457
27/03/202462
28/03/202473
29/03/202471
30/03/202442
31/03/202459
Message 2 of 8
177 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

View All