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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Werafa
Regular Visitor

Need help with cumulative totals start date

Hi all,

 

I'm new to PBI, have a strong background in VBA, and am a beginner on SQL & DAX.

I'm building a front end for an Access based BI system, and need advice on cumulative totals.

 

I've got my cumulative totals working (thanks to other posts here - using the Calculate, Sum, Filter All method).

I need a way to set the start date for the cumulative totals - and to ignore all prior values.

 

I wish to start by making cumulative values for each quarter - with each quarter starting from 0.

I would also like to make sure that the report will not break when it rolls over to a new financial year

 

can anyone suggest how I should do this?

Thanks

 

Werafa

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Werafa,

Based on my understanding, you can use TOTALQTD function. I create sample data including date and sales. I calculate the year to quarter total sales. Please see the following screenshot. It calculate cumulative total from every quarter. You can download the attachment to test.

1.PNG

If this is not what you want, you'd better create some sample table similar with your actual data. Then list the expected result, so we can post the solution which is close to your expected.

Thanks,
Angelia

 

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

Hi @Werafa,

Based on my understanding, you can use TOTALQTD function. I create sample data including date and sales. I calculate the year to quarter total sales. Please see the following screenshot. It calculate cumulative total from every quarter. You can download the attachment to test.

1.PNG

If this is not what you want, you'd better create some sample table similar with your actual data. Then list the expected result, so we can post the solution which is close to your expected.

Thanks,
Angelia

 

I think you are right - and thank you for the example - you have taught me several things from this.

 

one question if I may, you have used totalmtd in some measures, including 'this year sales'. I've understood this function to be 'month to date', and the measure name would seem to imply 'year to date' values. Have I missed something?

 

Thanks

Werafa

Hi @Werafa,

If you want to calculate the "month to date", please use TOTALMTD, it will calculated cumulative total form each month. You need to use TOTALYTD

Please see the formulas in the attchment Page2. I update it, please redownload and check.

SalesMonth-To-Day = TOTALMTD(SUM(Sales[SALE]),Sales[DATE])
This Year Sales = TOTALYTD(SUM(Sales[SALE]),'Calendar'[DATE])


Thanks,
Angelia

 

There are many standard inbuilt time intelligence functions that do this automatically, such as totalytd, totalqtd etc. I have a quick reference guide that outlines them all that you can download for free from my shop if you like. 

 

http://exceleratorbi.com.au/product/dax-reference-guide/

 

I also have a blog about custom time intelligence that should help you get your head around how it works

 

http://exceleratorbi.com.au/dax-time-intelligence-beginners/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks Matt,

 

I'll read and learn 🙂

This has been a crash course so far - (plenty of crashes of course)

 

hmm, I think it was your site that got me straight on the system I have got going.

Thanks.

Werafa

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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