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.
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.
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 : Model 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 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 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-selections/ (sorry it's in french)....