topic Re: Always display last 7 days of quantity depending on the most recent selected date in DAX Commands and Tips

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

daveinitiv — Mon, 05 Jun 2023 06:23:41 GMT

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!

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

Anonymous — Wed, 07 Jun 2023 02:46:35 GMT

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.

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

daveinitiv — Wed, 07 Jun 2023 06:00:05 GMT

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!

David

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

Ashok507 — Wed, 07 Jun 2023 09:04:51 GMT

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

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

Anonymous — Thu, 08 Jun 2023 05:37:46 GMT

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.

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

daveinitiv — Thu, 08 Jun 2023 09:26:08 GMT

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)