DAX – Properly calculating YTD

This seems like it should be straight forward, but it’s not. The documentation on the TOTALYTD function is not great.

Suppose I have a fact table with monthly revenue, and a standard date dimension.  The date dimension has a date value, of type date, for every day in the period, no holes.  Two formula’s to calculate the running  total of that data should be;

Revenue Calc 1 = TOTALYTD(SUM(Finance[actual revenue]),’Date'[FullDate])

Revenue Calc 2 = CALCULATE(SUM(Finance[actual revenue]),DATESYTD(‘Date'[FullDate]),ALL(‘Date’))

These two formulas are the same, however, the second one offers a little more flexibility, as multiple filters can be used.   I accidentally discovered something interesting about the difference in these two.  I was working on a file where I’d imported monthly revenue, with one line per month.  I had a date dimension with a relationship between the two.  Since I had only one revenue item per month, and thus one date, I could then do something stupid like this:

powerBI bad relationship

So in other words, every revenue item in the finance table would have it’s own context when TOTALYTD was evaluated, I’d get the monthly total, rather than the YTD as follows;

powerBI bad relationships revenue

All the while, the second calculation would work correctly as it changes the context in which the expression is evaluated.  In other words, it doesn’t care if you’re made mistakes in the relationships.

In other words, the cardinality of the relationship will define how TOTALYTD works.  Typically, the engine wouldn’t allow you do to this, as you’d have multiple dates in the fact table.  However, this was a good exercise in displaying the difference in context.

Here is the powerBI file if you want to see for yourself.

 

 

 

Far better an approximate answer to the right question, which is often vague, than the exact answer to the wrong question, which can always be made precise. -John Tukey
The plural of anecdote is not data. - John Myles White

Recent Posts

RSS PowerBI blog

  • New Power BI Premium summary and workload metrics available in the admin portal April 22, 2019
    We have rolled out a new high-level summary metrics experience in the Power BI Admin Portal . This new experience replaces the four summary tiles you previously saw : CPU, Memory thrashing, Memory usage and DirectQuery with wide range of metrics measuring the summary of usage in the last 7 days to better portray the health […]
  • Live now, full session catalog for Microsoft Business Applications Summit 2019 April 22, 2019
    Time to get excited – the full session catalog for Microsoft Business Applications Summit is here. Explore every breakout session and workshop coming to the conference, taking place in Atlanta, Georgia June 10 – 11, 2019. Get ready to flex your skills – and build new ones – with all things Power BI and beyond. […]
  • E-Mail Subscriptions for Paginated Reports is Now Available April 19, 2019
    E-mail subscriptions is one of the key features used by millions of SQL Server Reporting Services customers today. Our latest release for “New Feature Friday” is the support of Paginated Reports in e-mail subscriptions in Power BI. Now, for the first time, you can schedule an e-mail subscription with a PDF attachment of your full […]