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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EsmeeSnijders
Regular Visitor

Help with DAX - Week to date last selected Week

Hello,

 

I am creating a formula in DAX with which we want to calculate the sales week to date. 

 

We have a report with a date filter and an amount to be shown per weekday, only from the last filtered week. 

The date filter can be selecterd for a longer period than one week, and we are looking for a formula that returns the amount of sales for the last selected week, shown per weekday. 

 

Example:

DateWeekdaySalesAmount
15-05-2023Monday10
16-05-2023Tuesday25
17-05-2023Wednesday20
18-05-2023Thursday25
19-05-2023Friday10
20-05-2023Saturday25
21-05-2023Sunday30
22-05-2023Monday10
23-05-2023Tuesday30
24-05-2023Wednesday25
25-05-2023Thursday50
26-05-2023Friday10
27-05-2023Saturday35
28-05-2023Sunday10

 

In the report i have filtered the intire period. From 15-05-2023 till 25-05-2023. What I want is the sales amount, shown per weekday only of the last selected week. 

So what I expect to see in the report is: 

 

Monday

22-05-2023

Tuesday

23-05-2023

Wednesday 24-05-2023Thursday 25-05-2023
10302550

 

Can anybody please help me? 

 

Thank you so much!

1 ACCEPTED SOLUTION
Mahesh0016
Super User
Super User

Hello @EsmeeSnijders  i hope this helps you!THANK YOU!!

First your create WEEKNUM Column :

WeekNum = WEEKNUM('Table'[Date],2)
 
Then create measure  :
MXweek = MAX('Table'[WeekNum])

after that apply Matrix visual level filter 
Mahesh0016_1-1686210946096.png

 



Mahesh0016_0-1686210481234.png

 

@EsmeeSnijders THANK YOU!!

 

View solution in original post

2 REPLIES 2
Mahesh0016
Super User
Super User

Hello @EsmeeSnijders  i hope this helps you!THANK YOU!!

First your create WEEKNUM Column :

WeekNum = WEEKNUM('Table'[Date],2)
 
Then create measure  :
MXweek = MAX('Table'[WeekNum])

after that apply Matrix visual level filter 
Mahesh0016_1-1686210946096.png

 



Mahesh0016_0-1686210481234.png

 

@EsmeeSnijders THANK YOU!!

 

It works!! Thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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