Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum depending on hierarchy level

Hi everyone, I have a date hierarchy (Year, Month) and a fact (numbers). What I would like to do may sound simple, but I just cannot find a way. When the Hierarchylevel "Year" is displayed, I would...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I have found a solution which I would like to share:

     

    I used this formula

    MeasureName = CALCULATE(SUM('tableName'[factName]);LASTDATE('tableName'[DateColumn]))

    Together with a bar chart having on the x axis the DateColumn Hierarchy (in my case only Year and Month).

    The result is, that I have on the highest level the years in bars, where only the last month (December/12 for a closed year or the last month of the current year) is used for calculation. If I drill down one level, I see all the months for this specific year. 

    Thanks to everyone for your time and your help!

    Best

    J

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-eachen-msft ,

 

thank you for that, but this is not what I am looking for. I already have a date hierarchy (Year and Month) but as stated in my post below, I would like to calculate in the year perspective only the month = 12 and in the month perspective each month seperately.

 

Best

J

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I have found a solution which I would like to share:

 

I used this formula

MeasureName = CALCULATE(SUM('tableName'[factName]);LASTDATE('tableName'[DateColumn]))

Together with a bar chart having on the x axis the DateColumn Hierarchy (in my case only Year and Month).

The result is, that I have on the highest level the years in bars, where only the last month (December/12 for a closed year or the last month of the current year) is used for calculation. If I drill down one level, I see all the months for this specific year. 

Thanks to everyone for your time and your help!

Best

J