Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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")
Solved! Go to 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.
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.
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.
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.
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.
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.
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.
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:
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 : 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 :
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 ....
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)....