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.
That works! Thank you so very much. Still a bit clueless as to why previous setup didn't work, but i'll take it!
Thanks again.
I am wondering if perhaps your Year Entered field in your DimDateEntered table is perhaps a string and not numeric. If that is indeed the case, then the other way to fix it would be to use your original formula and at the end say = "2015" instead of = 2015.
Another possibility is that the field is a date-time value that is simply formatted to display the year.
Take a look at the table in the screenshots below. Row1 is a field that has type date-time and a locale-dependent format. [Year] is defined simply as follows:
Year = 'Query1'[Row1]
Thus, it is a date-time data type (as seen when it is highlighted), but its format is to display only the year portion.
[Actual Year] is defined as Greg_Deckler suggested, with the YEAR() function, and is a whole number data type with a whole number data format.
Type defines what the data is and what functions work with it, and how you must compare to it for equality. Format is simply how you display the underlying data. Changing the format of a date-time data type does not alter how you will test it for equality. Thus, even though my [Year] only shows the numbers 1899 and 1900, the underlying values are still date-times with the values from 1899-12-31 through 1900-01-04, and I would need to use those date literals in comparisons.
The actual value from [Row1]Note that this is YEAR( Query1[Year] ) - we're taking the year of a field that is formatted to display only year - we can do this because the data type is still date-timeHere we test for equality between [Year] and [Actual Year] - though their display format makes them appear to be the same, the underlying data types are different and the values are different.
Edit: Forgot to add this part
Greg_Deckler, comparing an integer to a string gives a type error, so we'd see different behavior than just a blank in the card - we'd get a visual can't be displayed error like below. The measure tests a field with the year as a string value against an integer literal. The visualization simply will not display.