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

Resolver III

## How to mark date as a Rolling 12 month?

I have a Date table and I want to mark weeks that fall within a rolling 12 months as rolling 12 dates. I want to mark them as 1 if they fall within the rolling 12 months and 0 if not. Any ideas would be greatly appreciated!

Thanks,

1 ACCEPTED SOLUTION
Resolver III

Thank you for your reply. I went for a different approach that was different than my original idea. I created a WeekOffset column in my Date table. This way I can filter my measures to be between -52 and 0 weeks.

WeekOffset =

VAR StartOfWeek = DimDate[date_field] - WEEKDAY(DimDate[date_field], 2)+1
VAR StartOfCurrentWeek = TODAY() - WEEKDAY(TODAY(), 2)+1

RETURN
(StartOfWeek - StartOfCurrentWeek) / 7
2 REPLIES 2
Super User

@nleuck_101 , Try a new column in date table

Week Type =
var _b12= date(year(today())-1, Month(Today()), day(Today()))
var _st = _b12 +-1*WEEKDAY(today() ,2)+1
var _end = today() + 7-1*WEEKDAY(today(),2)
Switch( True(),
[Date] >= _st && [Date] <= _end ,"Week In last rolling 12 month " ,
[Week Name]
)

or

Week Type =
var _st= date(year(today())-1, Month(Today()), day(Today()))
var _end = today()
Switch( True(),
[Date] >= _st && [Date] <= _end ,"date In last rolling 12 month " ,
[Week Name]
)

Resolver III

Thank you for your reply. I went for a different approach that was different than my original idea. I created a WeekOffset column in my Date table. This way I can filter my measures to be between -52 and 0 weeks.

WeekOffset =

VAR StartOfWeek = DimDate[date_field] - WEEKDAY(DimDate[date_field], 2)+1
VAR StartOfCurrentWeek = TODAY() - WEEKDAY(TODAY(), 2)+1

RETURN
(StartOfWeek - StartOfCurrentWeek) / 7

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 - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors