Forum Discussion
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")
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 lines
Sample of my date dimensionSample of my fact table - note 1 entry per month in 2014, 2 entries per month in 2015Relationships
You can change from year to year by just updating the visual-level filter on year.
16 Replies
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.
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.
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/
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]))
