Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
chinh_ho
Frequent Visitor

Cumulative values per month

Hi everyone,
I have a target goal sale for each saler in my company in 2022, I would like to calculate cumulativetarget line for each person. I have this picture for your reference. Please help me to calculate it. Thank you so much

Screenshot 2022-09-30 185812.png

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@chinh_ho , if you have a date with help from a date table

 

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

 

or

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

 

You can create date from month and year

 

Date = Date([year],[month],1)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@chinh_ho , if you have a date with help from a date table

 

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

 

or

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

 

You can create date from month and year

 

Date = Date([year],[month],1)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi amitchandak,
Thank you so much for your response. I can not use SUM function, because it is a single value. For example : Target sale in DimSaler in 2022

Micheal: $100,000

Jack: $120,000
My purpose is to have a cumulative target line by month to compare with actual sale.
Please help me calculate it. I also have a date table in my data model.

Thank you

@chinh_ho , do you have a yearly target and you want to show that cummulative

 

refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you so much for your quick response. I got a solution.

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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