cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors