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.

     

     

CJKPowerBI's avatar
CJKPowerBI
Icon for Helper I rankHelper 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'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 
  • CJKPowerBI's avatar
    CJKPowerBI
    Icon for Helper I rankHelper I
    3 years ago

    Jihwan_Kim 

     

    coming back with your formula, can we add a Filter over there to return only results from a column named TYPE and the selection to BALANCE?

     

    let me know pls.