Forum Discussion
point difference between individual points versus average trend line value
Hi, I need help with the DAX for what I am trying to do! I am showing annualized churn over time, by week. There is a date slider in my dashboard to select which weeks to view. Based on the weeks selected, the annualized churn for each week will be calculated and shown.
My churn measure formula is
ChurnCalc = CALCULATE((ChurnDigital[StopCopiesTotal]/ChurnDigital[AvgSubCopies]/ChurnDigital[AnnualizationFactor]*52)).
This ChurnCalc is also comprised of the following measures:
StopCopiesTotal = CALCULATE(SUM(ChurnDigital[StopCopies]),DATESBETWEEN(ChurnDigital[WeekId],[MinDate],[DistinctMaxDate]))
AvgSubCopies = [SubCopiesTotal]/[AnnualizationFactor]
AnnualizationFactor = DATEDIFF([MinDate],[DistinctMaxDate],WEEK) +1
DistinctMaxDate is a measure also:
DistinctMaxDate =
MAXX(KEEPFILTERS(VALUES('ChurnDigital'[WeekId])), CALCULATE([MaxDate]))
I have added the average trend line via the analytics tab in the Visualizations pane. What I need is the overall average of all the ChurnCalc column, to compare to each week's ChurnCalc value, and find the difference, but am having trouble calculating it as it is a measure.
Here is a table of my values for the date range selected 2/16/20 - 3/8/20 to give a better idea of how I am calculating things. So the overall ChurnCalc average for this period is 60.10%. The point difference then on Feb 23 would be 4.7.
| WeekId | AnnualizationFactor | SubCopies | StopCopies | ChurnCalc | StopCopiesTotalThruDate | DistinctMaxDate | MinDate |
| 2/16/2020 | 1 | 39745 | 459 | 60.05% | 459 | 2/16/2020 | 2/16/2020 |
| 2/23/2020 | 2 | 40139 | 392 | 55.40% | 851 | 2/23/2020 | 2/16/2020 |
| 3/1/2020 | 3 | 40522 | 600 | 62.66% | 1451 | 3/1/2020 | 2/16/2020 |
| 3/8/2020 | 4 | 41201 | 485 | 62.29% | 1936 | 3/8/2020 | 2/16/2020 |
Thanks for any help!
I was able to solve this by creating the following measures:
ChurnCalc average =AVERAGEX(ALLSELECTED('ChurnDigital'[WeekId]),CALCULATE([Churn]))Churn var to avg = ChurnDigital[Churn] - ChurnDigital[ChurnCalc average]
4 Replies
[Edited By Admin]
Hi there.
After having read all of this and seen the code...
Please reconsider!Especially when I saw this:
StopCopiesTotal = CALCULATE(SUM(ChurnDigital[StopCopies]),DATESBETWEEN(ChurnDigital[WeekId],[MinDate],[DistinctMaxDate]))
DATESBETWEEN is a time-intel function and it works well and correctly ONLY when used on the date column of a real Date table. You are using it on a fact table and in addition on a field that almost certainly is not a date column but a mere integer.
Please have a good look at the definition of the function:
https://dax.guide/datesbetween/
Check the arguments of it in particular.
Best
DI am lost as to why you are so horrified. My calculations using this function are working perfectly fine. WeekId is a datetime datatype that I have brought in from SQL.
The reason this calculation works is because it is calculating a cume between a given set of dates based on whatever the starting WeekId is that I have set in my date slider. The sum will vary. My StopCopiesTotal numbers are there for you in the table I attached.
Your response was very rude and not helpful at all.
[Edited by Admin]
I'm quite sure what you are doing is incorrect and potentially very dangerous.
No offense was meant. I was just trying to warn you against writing such things.
By the way, check to make sure you have proper Calendar dimension in your model and perform slicing directly on the fact table, it'll be hard for you to write correct formulas if you don't, especially when manipulation of time is involved.
Best
D
I was able to solve this by creating the following measures:
ChurnCalc average =AVERAGEX(ALLSELECTED('ChurnDigital'[WeekId]),CALCULATE([Churn]))Churn var to avg = ChurnDigital[Churn] - ChurnDigital[ChurnCalc average]
