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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.