Week vs prior week in visual based on slicer selection

[buttercream]

I have a visual showing number of orders.  I would like to break this up by last 7 days vs prior last 7 days (this week vs last week).  However, I'd like to start the week's count based on the date chosen in the slicer.

Example:

User selects 'Feb 5 2025' in slicer.  My bar graph visual should show two bars.  One with Feb 19-25 (week 1) and Feb 12-18 (Week 2).

How to accomplish?

[amitchandak · Accepted solution]

@buttercream , You can not accomplish a trend based on that,

You can have measures

Last week =
var _max = maxx(ALLSELECTED('Date'[Date]),'Date'[Date] ) -1
Var _min = Max -6
return
calculate([measure], filter(all('Date'), 'Date'[Date] >= _min && 'Date'[Date] <= _max))

Next Week=
var _max = maxx(ALLSELECTED('Date'[Date]),'Date'[Date] ) +7
Var _min = Max +1
return
calculate([measure], filter(all('Date'), 'Date'[Date] >= _min && 'Date'[Date] <= _max))

same way you can calculate dates to display on labels

[Anonymous]

Hi @buttercream,

Thanks @Ashish_Mathur  and @amitchandak for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @buttercream,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @buttercream,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar table
2. Create a relationship (Many to One and Single) from the Date column of your fact table to the Date column of the Calendar table
3. To your visual/table/slicer/filter, drag date from the Calendar table and select a date there
4. Write these measure

Total = sum(Data[Amount])

Total in this week = calculate([total],datesbetween(calendar[date],min(calendar[date])-6,min(calendar[date])))

Total in previous week = calculate([Total in this week],

datesbetween(calendar[date],min(calendar[date])-6,min(calendar[date])))

Hope this helps.

[amitchandak · Accepted solution]

@buttercream , You can not accomplish a trend based on that,

You can have measures

Last week =
var _max = maxx(ALLSELECTED('Date'[Date]),'Date'[Date] ) -1
Var _min = Max -6
return
calculate([measure], filter(all('Date'), 'Date'[Date] >= _min && 'Date'[Date] <= _max))

Next Week=
var _max = maxx(ALLSELECTED('Date'[Date]),'Date'[Date] ) +7
Var _min = Max +1
return
calculate([measure], filter(all('Date'), 'Date'[Date] >= _min && 'Date'[Date] <= _max))

same way you can calculate dates to display on labels