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.
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.
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
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.
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.
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.
