Forum Discussion
Dax for Current Week & Previous Week
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
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
Thanks Pat. I am checking your dax today and i will update after sometime.
Regards,
Venky
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
Not sure why that didn't work. Do you have other date related columns in the visual? In any case, another approach to consider is to add a DAX column with the week ending date and use that in your slicer.
WeekEnding = Table1[DayDate] + 8 - WEEKDAY(Table1[DayDate])Pat
mahoneypat Yes its there and the column name is weekdate in the line & Clustered Column chart visual. and also Weekdate is filtered as TOP 12 Latest Weekdate.
DayDate is my Slicer.
Pat, If you can see the attached screenshot yesterday, iam not using any dates apart from DayDate in my Testing Table Visual and even iam not able to get the data as per my requirement.
Thanks,
Venky