Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hi Mackie,
use the below measure.
sales = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-3,MONTH))
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.
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
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |