Reply
UnknownDev
Frequent Visitor

Cumulative Amount for the table and Chart

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?|
UnknownDev_0-1701960703911.png

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

UnknownDev_0-1701961341728.png

 


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:

UnknownDev_1-1701961377596.png

 


Thank you in advance for help!

1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

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:

vtangjiemsft_0-1702275347204.png

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. 

 

View solution in original post

2 REPLIES 2
avatar user
Anonymous
Not applicable

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:

vtangjiemsft_0-1702275347204.png

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
Frequent Visitor

Nobody can help me? 🙂

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)