Forum Discussion
Sum values with latest dates per year
hello all,
i have the below data
6/30/2020 - 12/31/2020 - 6/30/2021 - 12/31/2021 - 6/30/2022
Item 1 $100 $150 $200 $250 $300
Item 2 $200 $250 $300 $350 $400
and i would like to sum the data per yeat but:
1) to take in consideration the latest data (meaning December)
2) if there is no data for December like in 2022 then to take the previous meaning 06/30/22.
this is the outcome i would like:
| 2020 | 2021 | 2022 | |
| Item 1 | $150 | $250 | $300 |
| Item 2 | $250 | $350 | $400 |
thank you in advance.
Hi,
Please check the below pictures and the attached pbix file.
I suggest unpivotting a table like below, and then add [Year] column like below.
After loading the table into Power BI, create a measure like below, and put it into a matrix visualization.
Hi,
Thank you for your feedback.
Please check the below picture and the attached pbix file, that I added one more measure to fix the total row in the visualization.
6 Replies
- Jihwan_Kim
Super User
Hi,
Please check the below pictures and the attached pbix file.
I suggest unpivotting a table like below, and then add [Year] column like below.
After loading the table into Power BI, create a measure like below, and put it into a matrix visualization.
- CJKPowerBI
Helper I
thank you Jihwan_Kim the measure worked great.......
i have another question, how can i sum per year as now the "Row Subtotals" only sees the latest date and doesn consider the previous ones... see below
- Jihwan_Kim
Super User
Hi,
Thank you for your feedback.
Please check the below picture and the attached pbix file, that I added one more measure to fix the total row in the visualization.
- CJKPowerBI
Helper I
this is Jihwan_Kim its exactly what i needed... thank you