Forum Discussion

Helper I

3 years ago

Solved

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 $...

Super User

3 years ago

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
3 years ago
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
  3 years ago
  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.pbix18 KB
  - CJKPowerBI
    Helper I
    3 years ago
    hello Jihwan_Kim
    
    can we modify the below measures (they are your above measures but with different tables and columns names):
    
    1)
    Total per column per year: = sumx(ADDCOLUMNS(SUMMARIZE('Family situation PowerBI','Family situation PowerBI'[Institution],'Family situation PowerBI'[Date].[Year]),"@result",[ValueLatestDate(all Years)]),[@result])
    
    2)
    ValueLatestDate(all Years) = Var Max_Date = MAX('Family situation PowerBI'[Date])
    Var Latest_Value = CALCULATE(SUM('Family situation PowerBI'[Amount]),'Family situation PowerBI'[Date]=Max_Date)
    return Latest_Value
    
    to both include all the values from the current year...
    
    thank you
CJKPowerBI
Helper I
3 years ago
this is Jihwan_Kim its exactly what i needed... thank you