Time Intelligence: Calculating averages across various time-periods

[cwayne758]

Hi! 

Trying to calculate a respective average across different time periods (Delivery Dates ; Entered Dates). For Delivery Date, my formula works fine and gives correct result, but for Entered Date I get a BLANK result. Not sure why!(?)

Results of formula

formula

simple model

Thank you in advance!

[Greg_Deckler · Accepted solution]

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.

[greggyb · Accepted solution]

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.

[cwayne758]

Thanks for the assistance guys! 

@Greg_Deckler Please see below for formula..it is almost the same as the "Test Del 2015 avg. admin"  formula.

 "Test Ent 2015 Avg. Admin" formula

"Avg. Admin Score" is a measure

"Avg. Admin Score:" is a MEASURE

@greggyb

-There are no filters being applied.

-There is data with Entered Dates in 2015

** Thank you for the best practice advice! I have found myself caught in the struggle between ensuring reports are not made 'confusing' by the use of slicers and filters, while trying not to create technical debt for myself -____-

[Greg_Deckler]

Hmm, OK, what is interesting here is that with the one that is working, it is not directly related to GlobalPMS while the one that is not working is directly related to GlobalPMS in some way. Without the actual model, it is very difficult to figure out what is going on but I am curious as to how GlobalPMS and DimDateEntered are related to one another.

For example, I could envisage a scenario where GlobalPMS is related to DimDateEntered by some field that we will call X. It is theoretically possible that when filtering out only "2015" entries that there are no entries in 2015 where the values in X relate to GlobalPMS any longer and thus the blanks.

[cwayne758]

Yes the behavior is definitely peculiar! I have tried messing with the cardinality in the model but nothing changed.

GlobalPMS & DimDateEntered are related through the [DateEntered] column in GlobalPMS.

Perhaps worth mentioning that DimDateEntered in actually DateStream from Azure Data Marketplace, though I have added an extra column for Week. 

In regards to your example hypothesis; there is data after filtering out everything other than 2015.

[Greg_Deckler · Accepted solution]

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.

[cwayne758]

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.

[Greg_Deckler]

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.

[greggyb]

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.

[greggyb]

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.

[cwayne758]

This is literally killing me right now. I have learned my lesson. LOL 

[greggyb · Accepted solution]

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.

[cwayne758]

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. 

[cwayne758]

@greggyb I know that I could hardcode this measure to say something like:

2016 Negative Count = CALCULATE(COUNTA('Dim_Service'[MOVES_ID]), 'Dim_Service'[Positive/Negative] = "N", Dim_Date_Service[Year Entered] = 2016)

but after your advice last week, and my negative experiences with hardcoding time-intel function, I am trying to avoid when possible. 

Thank you again

[greggyb]

Negative Reviews =
CALCULATE(
    COUNTA( Dim_Service[Moves_ID] )
    ,Dim_Service[Positive/Negative] = "N"
)

Negative Reviews YTD =
TOTALYTD( [Negative Reviews], Dim_Date_Service[DateKey] )

Negative Reviews YTD Prior =
CALCULATE(
    [Negative Reviews YTD]
    ,SAMEPERIODLASTYEAR( Dim_Date_Service[DateKey] )
)

You can then set your filters based on current year only and the measure will give you last year's equivalent.

Let us know if you run into trouble.

[cwayne758]

Thank you very much for your well outlined solution! This was also a good lesson on how to use these powerful functions correctly.

To get my desired output, I have to have slicer set to Current Year, and also a seperate slicer set to the Current Week. I would like to avoid having to use the 'Week' slicer.

Side question: Is there a way to hard code the formula to show me the count for this current week in 2016, and then a seperate measure to show me the count for this current week in 2015(prior year)? Currently, the Prior Year measure shows me the count for the entire year, not the count for this period last year. Could this be accomplished with DateAdd?

Perhaps something like this: 

TEST Negative Service Breaks = CALCULATE(COUNTA(Dim_Service[MOVES_ID]), Dim_Service[Positive/Negative] = "N", DATEADD(Dim_Date_Service[DateKey], -7,DAY)) 

^^with the above code, the output is off.

Count of Moves_ID is the correct output.

[greggyb · Accepted solution]

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.

[cwayne758]

Boom! That's huge. Exactly what I was trying to do. Thank you so much!

Final questions (for today): Is there a way to set a custom parameter for Date.IsIn* functions? Can I set it to last 8 weeks (for example). **I know that may be tricky since we are only in week 2 of new year. 

Are there any online resources you recommend for better familiarizing myself with M/PowerQuery? 

Thank you again for the advice!

[greggyb]

Chris Webb has the best material on Power Query I've seen, though I've not searched much. It's largely a clone of F# with a lot of data munging libraries included, and I'm quite comfortable with functional languages in general (though not terribly familiar with F#), so I find myself satisfied with the formula reference.

No built-ins for "rolling X periods". Here's what I'd do - create a query that has unique combinations of, e.g. week and year, so you'd have 52 or 53 rows per year. Then sort ascending on week, then year, and add an index. You now have a unique key that represents any given week. You can then merge (Power Query's name for join) that week index into the date table. Now you can do a lookup for the [WeekIndex] of today, and set a flag if [WeekIndex] is within 8 of today.

This sort of index makes arbitrary date arithmetic very easy.

I'll put something together around that later if you need some help.

**Edit**: This Channel 9 video is pretty good if your comfortable with a fast paced intro, and have minimal experience with another programming language (especially a primarily functional one)..

[cwayne758]

I really appreciate your help @greggyb. Thanks again!

[Greg_Deckler]

What is the formula for the "Test Ent 2015 Avg. Admin:" measure? Is "Avg. Admin Score:" a measure and what is it's formula?