Cumulative Amount for the table and Chart

[UnknownDev]

Dear all,
Will be very thankful if you could help me to solve three problems related to cumulative amount.

I have calculated the cumulative amount for employees (taking into account the employee Start and End date). Used such formula:

Employees, Cumulative count =

VAR CalendarMin = MIN('Calendar'[Date])

VAR CalendarMax= MAX('Calendar'[Date])

RETURN

CALCULATE(

    [Employees count],

    Employees[Start Date] <= CalendarMax &&

    (Employees[End Date] >= CalendarMin|| ISBLANK(Employees[End Date])),

        DATESINPERIOD('Calendar'[Date], LASTDATE('Calendar'[Date]), -10, YEAR))

The Cumulative amount appears correctly: 11.
1. Is possible to show in the table "Total" 11 instead of 21?|

2.The same problem appears in "Card", it shows 21 instead of 11. 

Is it possible to somehow modify the formula to get the correct cumulative amount of employees?

3. I also need to show the result in bar charts but again the total amount appears incorrect - 21:

Thank you in advance for help!

[v-tangjie-msft · Accepted solution]

Hi @UnknownDev ,

According to your description, you want to show the Employees, Cumulative count value at total for the latest date right?

We can create a measure.

Measure = 
var _a=MAXX(ALL('Employees'),[Date])
var _b= CALCULATE([Employees, Cumulative count],FILTER(ALL('Employees'),[Date]=_a))
return IF(ISINSCOPE('Employees'[Date]),[Employees, Cumulative count],_b)

Output result:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[UnknownDev]

Nobody can help me? 🙂