Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
daveinitiv
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? 
 
 2023-06-05_08-21-00.jpg
 
If you need any additional info, I'm happy to provide. Thanks!
 
 
1 ACCEPTED SOLUTION

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)

vstephenmsft_1-1686202566193.png

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.

vstephenmsft_0-1686202557180.png

                                                                                                                                                         

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.           

View solution in original post

5 REPLIES 5
Ashok507
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

 

v-stephen-msft
Community Support
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.

vstephenmsft_0-1686105971719.png

 

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.           

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

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)

vstephenmsft_1-1686202566193.png

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.

vstephenmsft_0-1686202557180.png

                                                                                                                                                         

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.           

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)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors