Forum Discussion

hsalasde's avatar
hsalasde
Regular Visitor
2 years ago
Solved

Log and Delta Calculation

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 wi...
General Comment
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

amitchandak's avatar
amitchandak
Super User
2 years ago

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