Forum Discussion

mlmchilli's avatar
mlmchilli
Frequent Visitor
3 years ago
Solved

Filtering data based on last 7 days aggregate

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
Need Help

10 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      mlmchilli
      Frequent Visitor
      3 years ago

      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's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        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's avatar
    mlmchilli
    Frequent Visitor
    3 years ago

    Hello Yadong,

     

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

     

    Thanks  

    • v-yadongf-msft's avatar
      v-yadongf-msft
      Community Support
      3 years ago

      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

       

  • mlmchilli's avatar
    mlmchilli
    Frequent Visitor
    3 years ago

    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!