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(
ADDCOLUMNS(FILTER(_datetable,[TheDate]>_today-7 && [TheDate]<=_today),"Period","Last 1 week","Order",1),
ADDCOLUMNS(FILTER(_datetable,[TheDate]>_today-14 && [TheDate]<=_today),"Period","last 2 week","Order",2)

)

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors