Subset of "Sales" table including only latest 3 months data

[Mackie]

I'm studying Power BI by the official sample "Contoso Sales for Power BI Designer.pbix". https://www.microsoft.com/en-us/download/details.aspx?id=46801

How do I create a measure which is the subset of "Sales" table including only latest 3 months data?

Thank you.

[niteshtrehan89]

Hi Mackie,

use the below measure.

sales = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-3,MONTH))

[Greg_Deckler]

FILTER. Should be able to get the MAX Date in the table and then you could calculate 3 months before that and filter between those dates.

[Mackie]

I created a measure using this:

Sales 3-months = FILTER('Sales', 'Sales'[DateKey] >= DATE(YEAR(MAX('Sales'[DateKey])), MONTH(MAX('Sales'[DateKey]))-3, DAY(MAX('Sales'[DateKey]))))

But this returns an error. Can I borrow your help?  @Greg_Deckler 

[Greg_Deckler]

Sure @Mackie just listening to virtual MVP events and all the bars are closed in Ohio so I have nothing else to do!! 🙂

I'm downloading the file now and will take a look.

[Greg_Deckler · Accepted solution]

OK, your formula is excellent. If you were creating a table, it would be exactly what you want. But, if you truly want a measure, you need to aggregate over the table you have created. For example:

Sales 3-months = 
    SUMX(
        FILTER(
            'Sales', 
            'Sales'[DateKey] >= 
                DATE(
                    YEAR(MAX('Sales'[DateKey])), 
                    MONTH(MAX('Sales'[DateKey]))-3, 
                    DAY(MAX('Sales'[DateKey]))
                )
        ),
        [SalesAmount]
    )

Honestly, very nice. I was not super clear in my previous response and you executed on it very well.  Now, you could potentially run into issues with the -3 on month in the case that the last month happens to be January, February or March. You could also perhaps just set DAY to 1, just really depends on what you are going for exactly. Anyway, you are definitely on the right track.

[Mackie]

Thank you for your detailed explanation. I tried your measure and understood these things:
- If I want to create an aggregation, I should create a measure.
- If I want to create just a subset, I should create a table. Not a measure.

By the way, in Japan, everyone wears a face mask everywhere. That's a strange sight..
I hope things settle down soon.