cancel
Showing results 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

Helper III

## Calendar week range

Hello everyone,

I have a periodic table of dates that I want to use as follows. I am using this on slicer. but I want to calculate the last 1 and 2 weeks as calendar week like in powerbi. how can I do that? Last 1 week date range for today I want: 27.11.2022-3.11.2022

VAR _datetable = DimTarih
VAR _today = TODAY()
VAR _year = YEAR(TODAY())
VAR _thisyearstart = DATE(_year,1,1)
VAR _year1 = YEAR(TODAY())-1
VAR _yearstart = DATE(_year1,1,1)
VAR _month = MONTH(TODAY())
VAR _thismonthstart = DATE(_year,_month,1)
VAR _thisyearstart1 = DATE(_year,12,31)

RETURN UNION(

)

1 ACCEPTED SOLUTION
Community Support

Hi, @sonya7

You need to subtract the value of WEEKDAY(_today) in your original formula.

``````t1 =
VAR _datetable = DimTarih
VAR _today =
TODAY ()
VAR _year =
YEAR ( TODAY () )
VAR _thisyearstart =
DATE ( _year, 1, 1 )
VAR _year1 =
YEAR ( TODAY () ) - 1
VAR _yearstart =
DATE ( _year1, 1, 1 )
VAR _month =
MONTH ( TODAY () )
VAR _thismonthstart =
DATE ( _year, _month, 1 )
VAR _thisyearstart1 =
DATE ( _year, 12, 31 )
RETURN
UNION (
FILTER (
_datetable,
[TheDate]
> _today - 7
- WEEKDAY ( _today )
&& [TheDate]
<= _today - WEEKDAY ( _today )
),
"Period", "Last 1 week",
"Order", 1
),
FILTER (
_datetable,
[TheDate]
> _today - 14
- WEEKDAY ( _today )
&& [TheDate]
<= _today - WEEKDAY ( _today ) - 7
),
"Period", "last 2 week",
"Order", 2
)
)``````

Result:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi, @sonya7

You need to subtract the value of WEEKDAY(_today) in your original formula.

``````t1 =
VAR _datetable = DimTarih
VAR _today =
TODAY ()
VAR _year =
YEAR ( TODAY () )
VAR _thisyearstart =
DATE ( _year, 1, 1 )
VAR _year1 =
YEAR ( TODAY () ) - 1
VAR _yearstart =
DATE ( _year1, 1, 1 )
VAR _month =
MONTH ( TODAY () )
VAR _thismonthstart =
DATE ( _year, _month, 1 )
VAR _thisyearstart1 =
DATE ( _year, 12, 31 )
RETURN
UNION (
FILTER (
_datetable,
[TheDate]
> _today - 7
- WEEKDAY ( _today )
&& [TheDate]
<= _today - WEEKDAY ( _today )
),
"Period", "Last 1 week",
"Order", 1
),
FILTER (
_datetable,
[TheDate]
> _today - 14
- WEEKDAY ( _today )
&& [TheDate]
<= _today - WEEKDAY ( _today ) - 7
),
"Period", "last 2 week",
"Order", 2
)
)``````

Result:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

You are legend! thank you