Log and Delta Calculation

[hsalasde]

I'm pretty new to BI and have started using the VAR. I'm running a log count and I need to calculate the delta from the previous year for the same month. The results are to be displayed on a table with the rows for years and columns for months. I've managed to create a working count for log #s, but I'm having difficulty with the delta. (The delta is comprised from the difference between the current year's log count and the log count for the same month on the year prior).

Here is my DAX: 

Delta =

VAR LogIdCountCurrentMonth = CALCULATE(

    DISTINCTCOUNT('Surg Data'[Log #]),

    FILTER(

        ALL('Surg Data'[Date]),

        YEAR('Surg Data'[Date]) = YEAR(TODAY()) &&

        MONTH('Surg Data'[Date]) = MONTH(TODAY())

    )

)

VAR LogIdCountPreviousYearSameMonth =

    CALCULATE(

        DISTINCTCOUNT('Surg Data'[Log #]),

        FILTER(

            ALL('Surg Data'[Date]),

            YEAR('Surg Data'[Date]) = YEAR(TODAY()) - 1 &&

            MONTH('Surg Data'[Date]) = MONTH(TODAY())

        )

    )

RETURN

    IF(

        YEAR(TODAY()) = 2019 || ISBLANK(LogIdCountPreviousYearSameMonth),

        LogIdCountCurrentMonth,

        LogIdCountCurrentMonth - LogIdCountPreviousYearSameMonth

    )

Thank you for your help. I'm stuck!

[v-kaiyue-msft · Accepted solution]

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.

[hsalasde]

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?

[v-kaiyue-msft]

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.

[amitchandak]

@hsalasde , Make sure you create a date table and join it with date of your table and then you can use time intellignece

example measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

then you can take a diff

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s