Forum Discussion
Dax for Current Week & Previous Week
Hi,
My requirement got changed like below.
I have a Date slicer and my week starting from Monday to Sunday.
If user selects Today (May 24,2021) then i should be able to see the data only for Current week.
If user selects the future dates in the current week let's say May26th then i should be able to get the sum of May24,25 and May26th.
if user wants select the past dates like May 21st then i should be able to see the data for whole complated week i.e May 17th to May 23rd.
Any help (Super Users) on the Dax code would be appreciated
Below is the code somehow i have written for the below code and how can i concatenate current week dax
Weekly =
var _selectedDate = MAX(Table1[DayDate])
var _endOfWeekDate = _selectedDate + 7 - WEEKDAY(_selectedDate,2)
RETURN CALCULATE(Sum(Table1[TH]),Table1[DayDate] <= _endOfWeekDate
Table1 is nothing but my main table.
Thanks,
Venky
You could add a variable to calculate the most recent monday (relative to the MAX date), and then calculate your measure between the max date and that Monday date.
Weekly =
VAR _selectedDate =
MAX ( Table1[DayDate] )
VAR _endOfWeekDate =
_selectedDate + 7
- WEEKDAY ( _selectedDate, 2 )
VAR lastMonday =
CALCULATE (
MAX ( Table1[DayDate] ),
FILTER (
ALL ( Table1[DayDate] ),
Table1[DayDate] <= _selecteddate
&& WEEKDAY ( Table1[DayDate] ) = 2
)
)
RETURN
CALCULATE (
SUM ( Table1[TH] ),
FILTER (
ALL ( Table1[DayDate] ),
Table1[DayDate] <= _endOfWeekDate
&& Table1[DayDate] >= lastMonday
)
)Pat
11 Replies
Anonymous
Use DATEADD Function to get previous days data!
you can refer this video https://www.youtube.com/watch?v=dLON3mj0los&list=PLWQB3PEUJKRn1Y_s8whVWEoPPZa6Wm8B6&index=52
MFelix is this possible at a time to implement current and prevous week data to show (Completed week).
Regards,
Venky
You could add a variable to calculate the most recent monday (relative to the MAX date), and then calculate your measure between the max date and that Monday date.
Weekly =
VAR _selectedDate =
MAX ( Table1[DayDate] )
VAR _endOfWeekDate =
_selectedDate + 7
- WEEKDAY ( _selectedDate, 2 )
VAR lastMonday =
CALCULATE (
MAX ( Table1[DayDate] ),
FILTER (
ALL ( Table1[DayDate] ),
Table1[DayDate] <= _selecteddate
&& WEEKDAY ( Table1[DayDate] ) = 2
)
)
RETURN
CALCULATE (
SUM ( Table1[TH] ),
FILTER (
ALL ( Table1[DayDate] ),
Table1[DayDate] <= _endOfWeekDate
&& Table1[DayDate] >= lastMonday
)
)Pat
Hi Pat,
I tried to breakdown this formula but seems like not getting what iam expecting. Could you please help me to find exact one
05/27/2021 is my Max Date and also it falls in Current Week (my week:Monday to Sunday) so iam expecting the result is sum of (05/24/2021+05/25/2021+05/26/2021+05/27/2021)
if user select the any date in completed week (Completed week 05/17/2021 to 05/23/2021) then i should be able to get sum of whole completed week
Ex: if i select 05/23/2021 then sum of 05/17/2021 to 05/23/2021
and also iam i tried below logic but not working
Z_Selected Dte =Var _SDate = SELECTEDVALUE(Table1[DayDate])Var _EndDate = _SDate-WEEKDAY(_SDate,2)ReturnCALCULATE(SUM(Table1[TH]),FILTER(ALL(Table1[DayDate]),[z_Max_DayDate]<=_EndDate))Regards,
Venky
MFelix amitchandak v-yalanwu-msft mahoneypat
Hello Super users!! Any help to tune the dax.
Regards,
Venky
Hi Anonymous ,
Is the understanding of your requirements below correct?
User selection is Today = current week data until today
User selection higher than today = data from today until the day selected
User selection lower than today = full week of the day selected
Just a couple of questions how do you handle the selection of a day of the current week but lower than today? (do you pick up all the values of the week or only the ones until today or the selected day?
Are you abble to choose values after the current week? for example august date what values will you get for that selection?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Hi MFelix Apologies for late reply. Thanks for your response.
Hi Pat mahoneypat . Thanks so much my issue resolve with your code and have done little bit changes as per the requeirement.
Regards,
PV
