Forum Discussion

CJKPowerBI's avatar
CJKPowerBI
Icon for Helper I rankHelper 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            $...
General Comment
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    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.

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    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.

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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's avatar
CJKPowerBI
Icon for Helper I rankHelper 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