Forum Discussion
running total
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.
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/
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.
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.
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]))
