cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Always display last 7 days of quantity depending on the most recent selected date

Hi all,

I'm struggling to dynamically always display the last 7 days of quantity sold in a Visual.

I have tried with the following measure:
Quantity last week = calculate([Quantity], datesbetween(Sales[Date], max(Sales[Date])-7, max(Sales[Date])))

But the visuals still show all values starting from the beginning of the year. What am I doing wrong?

If you need any additional info, I'm happy to provide. Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @daveinitiv ,

For showing only the last 7 days, you could create a measure for filtering.

``````Measure = var _max=MAXX(ALL('Table'),[Date])
return IF(_max>=MAX('Table'[Date])&&_max-7<MAX('Table'[Date]),1)``````

After putting the measure into the visual-level filters and setting up show items when the value is 1, the visual shows the last 7 days only.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Frequent Visitor

Last7day_qty=

VAR datetb= datesinperiod(dimtable[date],max(dimtable[date],-7,day)

VAR last7qty=calculate ([ total quantity],datetb)

VAR selection _fliter=selected value (dimtable [ date]

VAR max_date_last7qty=MAXX (SUMMARIZE (dimtable, dimtable [ date],"maxdate",MAX(dimtable [date])),[total quantity])

return

IF(selection_filter,last7qty,max_date_last7qty)

Feel free to contact:6304729084

Community Support

Hi @daveinitiv ,

You should try

``Quantity last week = CALCULATE([Quantity],FILTER(ALLSELECTED('Table'),[Date]>MAX('Table'[Date])-7&&[Date]<=MAX('Table'[Date])))``

Here's an example for this measure.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Stephen,

thanks a lot for your efforts!

But I need something different, actually (sorry I wasn't 100% clear about that, I just noticed):

I want the visual to display to always only the last 7 days in dates. So the daily value for each datapoint, but in the date column only the last 7 days displayed.

I know there is a possibility to achieve a similar thing via the visual filters, but this gives me not enough flexibility (it always calculates last 7 days from today, so when I change the filtercontext to, say, 2 months ago, nothing will be displayed anymore).

It should always work like this: maximum date selected minus 7 on the date value. if no filter selection is made, max date in sales data minus 7 days.

I hope it was kind of clear (sorry, english is not my native language).

Thank you!

BR

David

Community Support

Hi @daveinitiv ,

For showing only the last 7 days, you could create a measure for filtering.

``````Measure = var _max=MAXX(ALL('Table'),[Date])
return IF(_max>=MAX('Table'[Date])&&_max-7<MAX('Table'[Date]),1)``````

After putting the measure into the visual-level filters and setting up show items when the value is 1, the visual shows the last 7 days only.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Stephen,

that's brilliant thanks, I was able to get the solution with a slightly modified formula, which I would never arrived at without your help!

This was the final one that got it solved:

D_Day_last_7 = VAR _max= calculate(max(Sales[Date]),all(Sales[Date])) RETURN if(_max >= calculate(max(Sales[Date]),filter(Sales,[Quantity]>0))&&_max-7<=calculate(max(Sales[Date]),filter(Sales,[Sales]>0)),1)

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors