Forum Discussion
Time Intelligence: Calculating averages across various time-periods
Ah, I use DateStream as well. I am wondering if you might try this. In your GlobalPMS table, create a calculated column such as:
Year Entered = YEAR([DateEntered])
Make sure it is a numeric field.
Then, change your formula to:
TEST Ent 2015 Avg. Admin: = CALCULATE([Avg. Admin Score:],GlobalPMS[Year Entered] = 2015)
And see if that comes back correctly.
Something that might be helpful in the future.
We (my peers and I who work for a BI consultancy), try to avoid calculated columns as much as possible, and similarly to avoid setting custom formatting in Power BI / Power Pivot / Tabular on fields. If you have a field that you want to display a certain way, it's usually because you want to use it as a label somewhere - that's why you want to control its display. If it's a label, it should either be a raw number (rare, the only common example I can think of is year - even decimal or float types are best avoided because you can display fewer significant digits than exist) or a string. Anything else can and will not be consistent due to locale and region settings on the client computer. Strings will always display identically. Whole numbers will potentially have a different 1000 separator. Dates will never display the way you want unless they are formatted as strings.
Additionally, calculated fields do not benefit from the same level of compression as "native" fields, for lack of a better term. This can potentially lead to memory and performance pressure.
Since calculated columns are only recalculated at model refresh time, there's no reason for them not to be calculated at the source or in ETL.
If you handle all your calculated fields and such in Power Query, you'll help to avoid this sort of situation.
Several questions here.
Year + Week slicer
Several options.
You could use a YearWeek display field, e.g. "2016 W02", allowing just a single slicer to be selected. Also much less opportunity for confusion on labels. Some people don't like this, though; that's fine.
You could create a flag field (use PowerQuery Date.IsIn* functions) that is True for the current week of the current year, then just set your filters to 'True' for that report sheet or individual visualization. Since you're likely refreshing your model regularly, this will be up to date.
Hard code current week filter
See the second paragraph in the previous section.
SAMEPERIODLASTYEAR() will work just fine with the filter context set by the CurrentWeekFlag field. No need to make a half dozen versions of every measure.
Cut off past today's date
Use a flag similar to above for YTD. Again, it plays well with other filters and the measures as defined.
The definition of the measure you're showing is not the definition for the non-functional measure.
Do you have any visual-, page-, or report-level filters in place that would remove all results with Entered dates in 2015?
Do you actually have data with Entered dates in 2015?
Without some sample of the data, it's difficult to diagnose what might be going wrong.
Additionally, it's not commonly a good pattern to write measures with literal filter values in them for purposes like this. I assume that next year you'd like to change all of these to 2016, and you'll have to manually update each measure definition. I'd suggest writing simpler measures without filtering logic hard-coded in and you can use visual-, page-, and report-level filters as necessary to restrict the data set considered.
This is literally killing me right now. I have learned my lesson. LOL
Something that might be helpful in the future.
We (my peers and I who work for a BI consultancy), try to avoid calculated columns as much as possible, and similarly to avoid setting custom formatting in Power BI / Power Pivot / Tabular on fields. If you have a field that you want to display a certain way, it's usually because you want to use it as a label somewhere - that's why you want to control its display. If it's a label, it should either be a raw number (rare, the only common example I can think of is year - even decimal or float types are best avoided because you can display fewer significant digits than exist) or a string. Anything else can and will not be consistent due to locale and region settings on the client computer. Strings will always display identically. Whole numbers will potentially have a different 1000 separator. Dates will never display the way you want unless they are formatted as strings.
Additionally, calculated fields do not benefit from the same level of compression as "native" fields, for lack of a better term. This can potentially lead to memory and performance pressure.
Since calculated columns are only recalculated at model refresh time, there's no reason for them not to be calculated at the source or in ETL.
If you handle all your calculated fields and such in Power Query, you'll help to avoid this sort of situation.
Hey there, thank you for your help the other day. I wanted to throw a similar question your way...
What is the best practice for using SamePeriodLastYear ; PriorYear ; and/or DateAdd? When using time intelligence, should one refrain from using Time-Slicers? My issue is that I need to provide a slicer for years (for my end users), but also want to show metrics for the same period in the prior year.
I am trying to do a simple calculation: YTD Count of Negative Reviews (2016) & Same Period Last Year Count of Negative Reviews (same period in 2015)
YTD Count of Negative Reviews = TOTALYTD(COUNTA(Dim_Service[MOVES_ID]), Dim_Date_Service[DateKey], Dim_Service[Positive/Negative] = "N") -----> this gives me the correct output.
Same Period Last Year Count: = CALCULATE(COUNTA(Dim_Service[MOVES_ID]), Dim_Service[ISSUE] = "N", DATEADD(Dim_Date_Service[DateKey], -1, YEAR)) -----> output is BLANK
Report Context: I am currently using a Year Slicer on the canvas. When I select "2016", my YTD count appears. Without the yera selection, it is blank. The Prior Year measure is always blank.
