Filtering data based on last 7 days aggregate

[mlmchilli]

Hello!

I'm hoping somebody can help!

I have data at a daily level in my fact. I would like to be able to filter months and show all days in that month where the sum of sales in the last 7 days for a month are over £1000. It is important to show each day in a table along with the daily sales value for all qualifying months.

The metric below will give me the salesamount for the last 7 days, however, if I use this as a filter (>1000) for my table I only get the days that are over £1000 and not all the days of the month that has total sales of £1000

7DaySales =

CALCULATE

(

    SUM (Sales[SalesAmount]),

    FILTER('Calendar','Calendar'[FullDate] >= TODAY()-7)

)

Any help is appreciated!

Thanks

r

[mlmchilli]

Hello Yadong,

Apologies for the delay. I'm going to mark this as the accepted solution as it clearly works.

We are still trying to get this into our model as typically the model is a little more complicated than the simplistic example given here but we shall get it sorted.

Thanks for your help and guidance!

[mlmchilli]

Hello Yadong,

We are trying to apply this to our model. Could you please explain what EARLIER does?

Thanks  

[v-yadongf-msft]

Hi @mlmchilli ，

EARLIER returns the current value of the specified column in an outer evaluation pass of the mentioned column.

EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.

EARLIER is mostly used in the context of calculated columns.

Please refer to:EARLIER function (DAX) - DAX | Microsoft Learn

Best regards,

Yadong Fang

[v-yadongf-msft]

Hi @mlmchilli ,

As you mentioned "I would like to be able to filter months and show all days in that month where the sum of sales in the last 7 days for a month are over £1000", I want to confirm with you ,do you want to filter the most recent month or all months? What is your expected output?

Can you share with me some screenshots of your data after hiding sensitive information?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

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

[mlmchilli]

Hello Yadong

Thanks for replying. Below is a simple version of the data. I have tried to simply it so have changed the scenario a little to suit. 

We would like to be able to filter the products that have over £1000 aggregate sales amount in the last 7 days of data. The key thing is we show all dates where products have over £1000 in the last 7 days and not just the individual days.

In the example below we would filter the grid in PowerBI to show Product 1 with all dates showing. Product 2 would not be shown even though the aggregate is over £1000 the last 7 days is under.

Hope this makes sense. Please let me know if anything isnt clear.

Thanks!

[v-yadongf-msft]

Hi @mlmchilli ,

This is my test table:

First, please create a new table to filter last 7 days:

NewTable = FILTER('Table','Table'[Date]>MAX('Table'[Date])-7 && 'Table'[Date]<=MAX('Table'[Date]))

Calculate sum of salesamount for last 7 days:

Last7days = CALCULATE(SUM('NewTable'[SalesAmount]),FILTER(ALL('NewTable'),'NewTable'[Product] = SELECTEDVALUE('NewTable'[Product])))

Put the measure just created into Filters:

Best regards,

Yadong Fang

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

[mlmchilli]

Hi Yadong,

Thanks for your reply. Based on your proposed solution we lose all of the days in the month for Product 1. We would like to show all days if the total of the last 7 days is over 1000. Is this possible?

Thanks

m

[v-yadongf-msft]

Hi @mlmchilli ,

I cannot understand  "lose all of the days in the month for Product 1", can you provide more details?

Best regards,

Yadong Fang

[mlmchilli]

In the screenshot of your results you only have the last 7 days of data for product 1 so Jan 1,2,3 are missing. We would need to see all days data where the total sales amount in the last 7 days is over £1000. Perhaps this is because you are filtering the fact table down to the last 7 days in your first statement?

[v-yadongf-msft · Accepted solution]

Hi @mlmchilli ,

Is this the result you want?

Best regards,

Yadong Fang

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