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

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

     

     

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

     

     

6 Replies

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