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

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

#### 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.