Need help with cumulative totals start date

[Werafa]

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

[v-huizhn-msft · Accepted solution]

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.



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

[Werafa]

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

[v-huizhn-msft]

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

[MattAllington]

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/

[Werafa]

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