Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
AC77
Frequent Visitor

Last Year Sales measure based on Trading Day rather than date

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





7 REPLIES 7
Anonymous
Not applicable

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

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




Anonymous
Not applicable

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.

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 

AC77
Frequent Visitor

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
AC77
Frequent Visitor

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
AC77
Frequent Visitor

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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