Forum Discussion
Log and Delta Calculation
Hi hsalasde ,
amitchandak makes a good point, I have another method here that I hope will be helpful to you.
Please try to modify the expression:
Delta = VAR LogIdCountCurrentMonth = CALCULATE( DISTINCTCOUNT('Surg Data'[Log]), FILTER( ALL('Surg Data'), YEAR(EARLIER('Surg Data'[Date])) = YEAR('Surg Data'[Date]) && MONTH(EARLIER('Surg Data'[Date])) = MONTH('Surg Data'[Date]) ) ) VAR LogIdCountPreviousYearSameMonth = CALCULATE( DISTINCTCOUNT('Surg Data'[Log]), FILTER(ALL('Surg Data'), YEAR('Surg Data'[Date])=YEAR(EARLIER('Surg Data'[Date]))-1&&MONTH('Surg Data'[Date])=MONTH(EARLIER('Surg Data'[Date])))) RETURN IF( YEAR(TODAY()) = 2019 || ISBLANK(LogIdCountPreviousYearSameMonth), LogIdCountCurrentMonth, LogIdCountCurrentMonth - LogIdCountPreviousYearSameMonth )
The final result is shown below.If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi hsalasde ,
amitchandak makes a good point, I have another method here that I hope will be helpful to you.
Please try to modify the expression:
Delta =
VAR LogIdCountCurrentMonth = CALCULATE(
DISTINCTCOUNT('Surg Data'[Log]),
FILTER(
ALL('Surg Data'),
YEAR(EARLIER('Surg Data'[Date])) = YEAR('Surg Data'[Date]) &&
MONTH(EARLIER('Surg Data'[Date])) = MONTH('Surg Data'[Date])
)
)
VAR LogIdCountPreviousYearSameMonth =
CALCULATE(
DISTINCTCOUNT('Surg Data'[Log]),
FILTER(ALL('Surg Data'),
YEAR('Surg Data'[Date])=YEAR(EARLIER('Surg Data'[Date]))-1&&MONTH('Surg Data'[Date])=MONTH(EARLIER('Surg Data'[Date]))))
RETURN
IF(
YEAR(TODAY()) = 2019 || ISBLANK(LogIdCountPreviousYearSameMonth),
LogIdCountCurrentMonth,
LogIdCountCurrentMonth - LogIdCountPreviousYearSameMonth
)
The final result is shown below.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for the time and effort to put this together. This is great! I'm getting an error when I try to use the EARLIER function. It keeps saying that it is referring to an earlier row context that does not exist. Do you know why this is the case?
Hi hsalasde ,
EARLIER is primarily used in the context of computed columns. Make sure you are using it in a calculated column, not measure.
EARLIER will succeed if the row context exists before the table scan begins. Otherwise, it returns an error.
It is recommended to use a variable (VAR) to save the value while it is still accessible, and then hide the required row context by the new row context to access the desired value. For detailed information, please refer to the documentation:EARLIER function (DAX) - DAX | Microsoft Learn.If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.