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

Anonymous
Not applicable

## calculated Column Flag of 1 to show previous 5 weeks based on current week and date

Hi Experts

Need a calculated column Flag to show 1 - where the date is in the previous 5 Weeks based on todays date and week no. The following is a image of my Table where is need to add the Previous 5 Weeks flag

1 ACCEPTED SOLUTION
Super User

@Anonymous , In case nothing is selected

This Week Today =
var _min1 = today() -WEEKDAY(today() ,2) +1 //Monday week start

var _min = _min1 - 28
var _max = _min1 +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

if you need based on selection

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _min1 = _max1 -WEEKDAY(_max1 ,2) +1 //Monday week start

var _min = _min1 - 28
var _max = _min1 +6

return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

2 REPLIES 2
Super User

@Anonymous , In case nothing is selected

This Week Today =
var _min1 = today() -WEEKDAY(today() ,2) +1 //Monday week start

var _min = _min1 - 28
var _max = _min1 +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

if you need based on selection

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _min1 = _max1 -WEEKDAY(_max1 ,2) +1 //Monday week start

var _min = _min1 - 28
var _max = _min1 +6

return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27304

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Anonymous
Not applicable

Hi Amit

Are the above measures calculated columns measures and what does not refer too in the first measure

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.