Forum Discussion
Helper IIIExtracting Corresponding Date from Totals
Welcome some help with this please!
I am having great problems!
I have some sample data:
| Product | 01/01/2023 | 01/02/2023 | 01/03/2023 | 01/04/2023 | 01/05/2023 | 01/06/2023 | 01/07/2023 | 01/08/2023 | 01/09/2023 | 01/10/2023 | 01/11/2023 | 01/12/2023 | 01/01/2024 | 01/02/2024 |
| A | 12 | 55 | 22 | 33 | 22 | 33 | 22 | |||||||
| B | 11 | 59 | 33 | 33 | 21 | 2 | 22 | |||||||
| A | 22 | 23 | 35 | 2 | 11 | 3 | ||||||||
| A | 22 | 24 | 89 | 21 | 1 | |||||||||
| A | 22 | 36 | 9 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | |||
| B | 58 | 2 | 2 | 8 | 8 | 5 |
Basically I am after a table that will then give me for each Product, the totals per month such as this:
| Product | 01/01/2023 | 01/02/2023 | 01/03/2023 | 01/04/2023 | 01/05/2023 | 01/06/2023 | 01/07/2023 | 01/08/2023 | 01/09/2023 | 01/10/2023 | 01/11/2023 | 01/12/2023 | 01/01/2024 | 01/02/2024 |
| A | 12 | 99 | 81 | 68 | 31 | 4 | 68 | 6 | 27 | 111 | 24 | 4 | 3 | 3 |
| B | 11 | 117 | 2 | 35 | 41 | 29 | 7 | 22 | 0 | 0 | 0 | 0 | 0 | 0 |
And then finally, a table that gives me the maximum value, and the corresponding date:
| Product | Max Value | Date |
| A | 111 | 01/10/2023 |
| B | 117 | 01/02/2023 |
Grateful for any help
So, first you need the TOTAL for each Product and Month, right? With that, you can select the Month with the highest sales?
MaxValue Per Product = MAXX(SUMMARIZE('Table','Table'[Date].[Year],'Table'[Date].[Month],'Table'[Product]), CALCULATE(SUM('Table'[Value])))Max Value Date = VAR MaxValue = MAXX(SUMMARIZE('Table','Table'[Date].[Year],'Table'[Date].[Month],'Table'[Product]), CALCULATE(SUM('Table'[Value]))) RETURN CALCULATE(VALUES('Table'[Date]), FILTER(ALL('Table'[Date]), [MaxValue Per Product] = MaxValue))With my dummy file I got the result as expected I think.
Data (not that in February Product A sums up 14, which is the higher value for Product A, while Product B sums up 16 in April):
Outcome:
If that's not what you want, then please could you share your .Pbix?
7 Replies
- danextian
Super User
Hi TimK ,
The current format of your data source is not desirable for reporting purposes. I would unpivot it first before doing anything else. Here's a sample pbix using data. https://drive.google.com/file/d/1uD8G8cNNf-PHif8vehBHH7Zx90rkFDVG/view?usp=sharing
As for the max value? How is it exactly computed? Max value per month per product? Etc.
AleResolver II
As the colleague danextian mentioned, ideally you should unpivot your data in order to have 1 column with all the dates.
Once this is done, you can achieve what you want by doing the following:
1 Measure for Max Value:
MaxValue Per Product = MAX('Table'[Value])1 Measure to get Date for that Max Value:
Max Value Date = VAR MaxValue = CALCULATE(MAX('Table'[Value]), ALLSELECTED('Table'[Date])) RETURN CALCULATE(VALUES('Table'[Date]), FILTER(ALL('Table'[Date]), [MaxValue Per Product] = MaxValue))In your table visual, just add the Product to the Rows and these 2 Measures to the Values.
- TimK
hi Ale
Thanks for that......
So i probably was not clear - sorry......
First we need to create data as per the second table - so each product listed once, with the TOTALS for each product....
Then the challenge is to find the month with the highest TOTAL, not initial figure.....
Does that make sense? Sorry. 🙂- Ale
So, first you need the TOTAL for each Product and Month, right? With that, you can select the Month with the highest sales?
MaxValue Per Product = MAXX(SUMMARIZE('Table','Table'[Date].[Year],'Table'[Date].[Month],'Table'[Product]), CALCULATE(SUM('Table'[Value])))Max Value Date = VAR MaxValue = MAXX(SUMMARIZE('Table','Table'[Date].[Year],'Table'[Date].[Month],'Table'[Product]), CALCULATE(SUM('Table'[Value]))) RETURN CALCULATE(VALUES('Table'[Date]), FILTER(ALL('Table'[Date]), [MaxValue Per Product] = MaxValue))With my dummy file I got the result as expected I think.
Data (not that in February Product A sums up 14, which is the higher value for Product A, while Product B sums up 16 in April):
Outcome:
If that's not what you want, then please could you share your .Pbix?
- TimK
hi Ale
BINGO - you have done it - thank you very much......
I may have supplementary questions in a few days!!
Thank you for your help with this
