Hey guys,
I thought I had a good approach for a formula, but in some cases I get this error message "A table of multiple values was supplied where a single value was expected". Here's what I need:
There is a table with Product names like this:
| Product name | Start Date |
| A1 | 01.01.2020 |
| B1 | 01.07.2021 |
| C1 | 01.08.2022 |
| A2 | 01.09.2020 |
And another table with something like sales:
| VOL | Product Category | Sales Date |
| 3 | A | 07.02.2020 |
| 1 | A | 08.02.2020 |
| 6 | A | 07.09.2020 |
| 3 | A | 09.02.2020 |
| 3 | A | 10.09.2020 |
| 4 | B | 01.07.2021 |
Please note: The tables are not directly connected, but through another table so that A1, A2, ... belong to A.
What I want Power BI to do is to look for the maximum VOL and return the sum for the following 6 days. But first, he has to check if the volume of product category A belongs to A1 or A2, which I did with DATESBETWEEN Min(Start Date) and Min(Start Date)+60 days.
I tried this formula:
VOL_max =
CALCULATE (
SUM ( 'Sales'[VOL] ),
DATESBETWEEN(Sales[Sales Date],LOOKUPVALUE(Sales[Sales Date],'Sales'[VOL],CALCULATE(max(Sales[VOL]),DATESBETWEEN(Sales[Sales Date],min('Products'[Start Date]),min('Products'[Start Date])+60))),LOOKUPVALUE(Sales[Sales Date],Sales[VOL],CALCULATE(max(Sales[VOL]),DATESBETWEEN(Sales[Sales Date],min('Products'[Start Date]),min('Products'[Start Date])+60)))+6))
As I said, this doesn't work when there is no unique maximum volume because LOOKUPVALUE returns this multiple values. For me, it would be perfect, when Power BI would go with the first maximum by date. Do you have any idea how to fix this?
