Sum depending on hierarchy level

[Anonymous]

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 like to sum only the facts which have month = 12 and display them in the chart.
When the Hierarchylevel "Month" is displayed, I would like to sum the facts by the month.

Is something like this possible?

Thank you in advance
J.

[Anonymous]

Hello everyone,

thank you @amitchandak, @parry2k, @v-eachen-msft. for your considerations.

Unfortunately, I could not make your code run. 

I would like to share with you some example data and explain the requirement in greater detail:

Here, you can see the data. 
What I would like to have is a stacked Bar chart with the hierarchy (Month and Year). First, the year is shown (2019) and here I would like to have only the month 12 summed. When I click on the year 2019 (meaning I will show the months), I would like to have all months summed up.

So, the result should be

2019: CEO = 11, Sales = 8, Production = 3, ProductionUS = 3, SalesUS = 8

After clicking on the bar 2019, I would like to have 12 stacked bars (month 1-12) with the values, summed by categorie1 and categorie2:

month 1 = ProductionUS = 45, SalesUS = 25, CEO = 70, Sales = 25, Production = 45
month 2 = ProductionUS = 43, SalesUS = 24, CEO = 67, Sales = 24, Production = 43

and so on...

I hope I expressed it in a more clear way now. Please let me know if you need any further details and please apalogize my late reply.

Thank you in advance

Best

J

Sample data

[v-eachen-msft]

Hi @Anonymous ,

Could "Date Hierarchy" satisfy your requirements?

If not, you could provide sample data without any confidential information and explain your additional requirements.

[Anonymous]

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 · Accepted solution]

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

[amitchandak]

I tried something like this for my last period calculation. Just see if this can help

Last Period Employee = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
 CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Is filter to switch month and year.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[parry2k]

@Anonymous if you share sample data with expected output, it will help.