Forum Discussion
Helper IVTime Intelligence: Calculating averages across various time-periods
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!
19 Replies
- Greg_Deckler
Community Champion
What is the formula for the "Test Ent 2015 Avg. Admin:" measure? Is "Avg. Admin Score:" a measure and what is it's formula?
- greggyb
Resident Rockstar
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.
cwayne758This is literally killing me right now. I have learned my lesson. LOL
- greggyb
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
-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.
