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
wsloan311
Regular Visitor

running total

I'm trying to create a line report that compares cumulative totals by month as the x-axis.  I've created formulas to calculate 2015 and 2016 totals.  Problem is it only shows the total if I use the actual date as the x axis, when I try to use month it reverts back to a monthly total.  Anyone know what I'm doing wrong?  Here are my 2 formulas for what I'm trying to chart:

 

Cumulative2015 = TOTALYTD(COUNTA(AssessmentLogEntries[EncounterKey]),AssessmentLogEntries[ScheduledDate],AssessmentLogEntries[Year]="2015","12/31")

 

Cumulative2016 = TOTALYTD(COUNTA(AssessmentLogEntries[EncounterKey]),AssessmentLogEntries[ScheduledDate],AssessmentLogEntries[Year]="2016","12/31")

1 ACCEPTED SOLUTION

First, @fbrossard, wow you're totally right. I hadn't realized that effect in Power BI. I'll be honest that the majority of my time these days is spent in SSAS Tabular, and I utilize Power BI solely as a visualization layer. Thanks very much for clarifying these points for me.

 

Second, toward the requirement of the OP, rereading it seems that you're making things far too complicated on yourself. Here's a suggestion of the measures to use:

 

EncounterCount = 
COUNTA( AssessmentLogEntries[EncounterKey] )

EncounterCountYTD = 
TOTALYTD( [EncounterCount], DimDate[Date] )

Below are some images of my sample data and a chart behaving (I believe) exactly as you want.

 

My chart, months on the X axis, with two years plotted as separate linesMy chart, months on the X axis, with two years plotted as separate lines

Sample of my date dimensionSample of my date dimensionSample of my fact table - note 1 entry per month in 2014, 2 entries per month in 2015Sample of my fact table - note 1 entry per month in 2014, 2 entries per month in 2015RelationshipsRelationships

 

You can change from year to year by just updating the visual-level filter on year.

View solution in original post

16 REPLIES 16
Greg_Deckler
Super User
Super User

You are going to need to use an ALL or ALLEXCEPT most likely to prevent the context filtering from getting in the way. When you are switching to month, the context filtering is filtering your information to just that month, hence the month total. I think wrapping your formulas in a CALCULATE with an ALL(AssessmentLogEntries) in the filter clause should do it.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Like this?  I still get the same result as before

 

Cumulative2015 = CALCULATE(TOTALYTD(COUNTA(AssessmentLogEntries[EncounterKey]),AssessmentLogEntries[ScheduledDate],AssessmentLogEntries[Year]="2015","12/31"),ALL(AssessmentLogEntries[EncounterKey]))

It looks like you are using the time intelligence functions directly against your fact table. This is not supported usage. They "work" but often will function incorrectly compared to your intent.

 

The best practice, in general, and the use case laid out in the documentation for time intelligence functions is to use them only against a date dimension that has been marked as a date table.

 

Please reference the following two links to understand better how to implement time intelligence in Power BI / Power Pivot.

 

Time intelligence in Power Pivot.

 

Understanding and creating date dimension

I created a Date table and linked it to my table already, tried using those dates instead, nothing changed.

Now that you have a date table, have you considered changing your measure to use DATEADD instead of creating separate measures for the years. I have found that this method works better for comparing by month and even by week on the axis.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




no, I haven't considered that yet(dateadd), don't I need 2 measures to graph 2 separate lines?  How else would I compare last year to this year?

 

Thanks

The DATEADD would keep you from having to filter the page or report by year. You create your YTD measure then use it in a DATEADD, -1,year to come up with last year's YTD. You can go back as far as you need by subtracting more years and naming them "Previous Year YTD" or "2 Year Prior YTD" This would allow multiple years to be calculated on the same visual.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@kcantor You'd still need to handle setting the context for evaluation to current year. This isn't too hard to do in a measure, but it would make these measures applicable only to one use case.

The X axis here is just the month name. If it were month and year, you'd then need to set a year filter in the report anyway.

 

There is no single best method of solving problems. Yes, DATEADD() is a useful function offering flexibility in time intelligence scenarios. That is not an issue at question. The question is which solution offers the most convenience for this specific reporting scenario in context of the model and other reports that need to be developed. The right answer is the solution which offers the most reuse of components across reports.

First, @fbrossard, wow you're totally right. I hadn't realized that effect in Power BI. I'll be honest that the majority of my time these days is spent in SSAS Tabular, and I utilize Power BI solely as a visualization layer. Thanks very much for clarifying these points for me.

 

Second, toward the requirement of the OP, rereading it seems that you're making things far too complicated on yourself. Here's a suggestion of the measures to use:

 

EncounterCount = 
COUNTA( AssessmentLogEntries[EncounterKey] )

EncounterCountYTD = 
TOTALYTD( [EncounterCount], DimDate[Date] )

Below are some images of my sample data and a chart behaving (I believe) exactly as you want.

 

My chart, months on the X axis, with two years plotted as separate linesMy chart, months on the X axis, with two years plotted as separate lines

Sample of my date dimensionSample of my date dimensionSample of my fact table - note 1 entry per month in 2014, 2 entries per month in 2015Sample of my fact table - note 1 entry per month in 2014, 2 entries per month in 2015RelationshipsRelationships

 

You can change from year to year by just updating the visual-level filter on year.

Thanks for the help, think I'm getting close, I used these formulas and set everything up like you said, only problem is I don't understand how you get both lines for the different year.  I put the EncounterCountYTD in the value box and I just get this year, not this year and last year.

@wsloan311 Note how I've put DimDate[Year] as the legend entry. The labels from the field in the legend entry will all be plotted as separate lines.

 

**Edit** I notice that the images come across scaled down quite a bit in the in-browser viewer they've got on this forum. You can download a copy to get the original full res image that's easier to see what I'm doing.

Thanks alot greggyb, that worked perfectly.  I appreciate you taking the time.

@greggyb I know, it's little bit disapointing 🙂

Just you can not mark a table as a Date Dimension in Power BI Desktop. It's only possible via Power Pivot in Excel.

But you should create a date table in Power BI Dektop, just need to have a date column as a key and link your fact tables on it. Then you can use time intelligence function.

To implement a date table with Power Query, you can use this :

http://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/

or http://devinknightsql.com/2015/06/16/creating-a-date-dimension-with-power-query/

@fbrossard, you are absolutely correct. I hadn't even realized that 'Mark as Date Table' wasn't available in PBI Desktop yet. Thanks for pointing that out.

 

That being said, 'Mark as Date Table' mostly just enforces the requirements of a date dimension (there's the implicit ALL() I mentioned above, as well).

 

What are those requirements to make the time intelligence functions work properly? Glad you asked. A date table must have at least one column with date-time data type. This column must:

  • Have contiguous dates - no gaps between dates, regardless of whether there are date gaps in the fact table
  • Not repeat dates - every date must be covered, as mentioned above, but there must not be duplicate entries for any date - one and exactly one entry per date
  • Span from January 1 in the first year you have data to December 31 in the last year you have data

The requirements above are in general for time intelligence functions to behave appropriately.

Additionally, depending on usage, some edge cases may requre that the span be from January 1 in the year before the first year you have data through December 31 in the year after the last year you have data.

 

There is no requirement that the relationship be defined on the date column. This is an easy way, though, to guarantee that there are no duplicate dates.

I will often use a numeric date key rather than use a date to join on.

@greggyb you're right a date dimension should have, by definition, contiguous dates. And in tabular many time intelligence function works only on contiguous date. And this is true, the best practice is to have an integer as key column by formating your date as YYYYMMDD. But in Power Desktop, as you cannot mark your table as a Date Dimension, time calculation doesn't work properly. For example see the model above : ModelModel In this model, my fact table is link to my date table by the integer key. On my fact table, i just add the caculated measure SalesAmount = TOTALYTD(SUM('fac Sale'[Amount]);'dim Calendar'[Date])

 

Just a simple visual on it :

 

YTD Calculation ErrorYTD Calculation Error Oh damned, that doesn't work properly.... So, I modify my query on fac Sale to add a calculated column based on my Date Key to have a Date and, and then modify my relationship between my fac table and my date dimension by using the date column (and not the integer key) and waouh it works ....   YTD Correct CalculationYTD Correct Calculation

Moreover, if you have requirements to calculated over non-contiguous dates, for example when you have to calculate a Year over Year on non contiguous date selection see https://fbro.wordpress.com/2013/02/19/powerpivot-sameperiodlastyear-on-non-contiguous-date-selection... (sorry it's in french)....

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.