Power BI - Get ("running") minimum values for all dates per category
I have two tables. Table1: article numbers, categories Table2: article numbers, dates, prices
The two tables are connected based on the article numbers. Each item number occurs once in the Table1.
I would need a calculated table where all the dates and the minimum cost values would be listed per category.
The problem is that not all articles within a category have the same date. But I need the minimum value at all times. In other words, if I have a cost value of $10 for article 'A' on the first of January, then if I only have cost data for article 'B' for the second of January (e.g.: $12), then $10 should also be included in the table for the second of January.
Table1:
| Article | Category |
|---|---|
| A | cat1 |
| B | cat1 |
| C | cat2 |
| D | cat2 |
| E | cat3 |
Table2:
| Article | Date | Price |
|---|---|---|
| A | 01.01.2023 | $10 |
| A | 03.01.2023 | $10 |
| B | 02.01.2023 | $12 |
| B | 03.01.2023 | $11 |
| B | 04.01.2023 | $9 |
| C | 01.01.2023 | $10 |
| C | 02.01.2023 | $10 |
| D | 01.01.2023 | $11 |
| D | 04.01.2023 | $9 |
| F | 01.01.2023 | $8 |
| F | 02.01.2023 | $9 |
| F | 03.01.2023 | $7 |
Table result:
| Category | Date | MinPrice |
|---|---|---|
| cat1 | 01.01.2023 | $10 |
| cat1 | 02.01.2023 | $10 |
| cat1 | 03.01.2023 | $10 |
| cat1 | 04.01.2023 | $9 |
| cat2 | 01.01.2023 | $10 |
| cat2 | 02.01.2023 | $10 |
| cat2 | 04.01.2023 | $9 |
| cat3 | 01.01.2023 | $8 |
| cat3 | 02.01.2023 | $8 |
| cat3 | 03.01.2023 | $7 |
How can I create this calculated table?
It could look like:
TableResult =
var tbl =
ADDCOLUMNS(
SUMMARIZECOLUMNS('Table1'[Category], 'Table1'[Article], 'Table2'[Date] ),
"Price",
var thisDate = [Date]
var thisArt = [Article]
var fltr = FILTER('Table2', 'Table2'[Article] = thisArt && 'Table2'[Date] <= thisDate)
return CALCULATE( MIN('Table2'[Price]), TOPN(1, fltr, [Date], DESC) )
)
return GROUPBY(tbl, [Category], [Date], "MinPrice", MINX(CURRENTGROUP(), [Price]))
