Same day Last year Measure | Unable to calculate total

[liboyjoseph]

Hi Guys,

I am facing challenges with the same day last year (not same period last year) numbers, Below is my measure and I can the calculation is perfect. but I'm unable to calculate the total for the matrix, Can someone help me with this, please?

For example, we are comparing sales Day to Day, Jan 2019 1st is Tuesday, so we do comparison Tuesday to Tuesday Last Year Jan-2018 1st week which is Monday. When I compare 01-01-2019 (Tuesday) it should return Tuesday from 1 Week of -Jan-2018. 

I have found a solution for above, 

SameDayLY =
CALCULATE (
SUM ( 'Actual'[Actual]),
FILTER (
ALL ( 'Date' ),
YEAR ( 'Date'[Date] )
= YEAR ( MAX ( 'Date'[Date] ) ) - 1
&& WEEKNUM ( 'Date'[Date] ) = WEEKNUM ( MAX ( 'Date'[Date] ) )
&& WEEKDAY ( 'Date'[Date] ) = WEEKDAY ( MAX ( 'Date'[Date] ) )
)
)

It works for me, but it doesn't give Total for my matrix. Does anyone know the best practice to create a measure for the business scenario? Please help @ 

[v-xuding-msft]

Hi @liboyjoseph ,

It is caused that the row context ( "Month" in your sample) affect the measure of SameDayLY. You coud create a formula using the function of SUMX to calculate the total values.

Measure = SUMX(Actual,[SameDayLY])

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

[liboyjoseph]

It doesn't work for me. 

SameDayLY1 =
CALCULATE (
SUMX('Actual','Actual'[Actual]),
FILTER (
ALL ( 'Date' ),
YEAR ( 'Date'[Date] )
= YEAR ( MAX ( 'Date'[Date] ) ) - 1
&& WEEKNUM ( 'Date'[Date] ) = WEEKNUM ( MAX ( 'Date'[Date] ) )
&& WEEKDAY ( 'Date'[Date] ) = WEEKDAY ( MAX ( 'Date'[Date] ) )
)
)

[v-xuding-msft]

Hi @liboyjoseph ,

You need to create a new measure with SUMX function rather than change SUM to SUMX.

Measure = SUMX(Actual,[SameDayLY])

I attached my sample that you can reference.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

[liboyjoseph]

Really appreciate your quick response and thanks a lot for the help. I think there's a problem with my date. It doesn't work. I will keep trying to find a solution. Please advise if you feel something needs to be corrected from my end.

[liboyjoseph · Accepted solution]

Finally, I've found a solution. Below measure works for me. 

SameDayLYy =

CALCULATE(SUM ('Actual'[Actual]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date],1,DAY)))

Thank you.