cancel
Showing results for
Did you mean: Frequent Visitor

Comparing same period to last year. Monday TY to Monday LY.

Good Day

I've added the below measure. However, it is comparing Tuesday, 9 Nov 2021 to Monday, 9 Nov 2020.

I need to compare Tuesday, 9 Nov 2021 to Tuesday, 10 Nov 2020.

How can I fix the measure below?

MTD Net Sales LY =
VAR _TODAY = TODAY()
VAR _LY = EDATE(_TODAY,-12)
RETURN
CALCULATE(
'Combined Sales'[Sum of NetSalesValue 2],
SAMEPERIODLASTYEAR('Calendar'[Date]),
'Calendar'[Date] <= _LY
)

1 ACCEPTED SOLUTION  Community Support

Hi @bvanderwatt ,

Measure =
VAR ThisFY =
CALCULATE ( MAX ( 'Calendar'[FY] ), 'Calendar'[Date] = TODAY () )
VAR PreFY =
CALCULATE (
MAX ( 'Calendar'[FY] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[FY] < ThisFY )  ---------modified
)
VAR FirstDay_ThisMonth =
EOMONTH ( TODAY (), -1 ) + 1
VAR Yesterday_ =
TODAY () - 1
CALCULATE (
'Calendar'[Date] = FirstDay_ThisMonth
)
CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
'Calendar'[FY] = ThisFY,
)
VAR PreFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
FILTER (
----------------modified
ALL ( 'Calendar' ),
'Calendar'[FY] = PreFY
)
)
RETURN
ThisFYPerformance - PreFYPerformance

If you need to limit to the current month, the slicer is not needed when using this measure.

If you want to dynamically change the filtered month, try this measure:

Measure2 =
VAR SelectedFY =
MAX ( 'Calendar'[FY] )
VAR PreFY =
CALCULATE (
MAX ( 'Calendar'[FY] ),
FILTER ( ALL ( 'Calendar'[FY] ), 'Calendar'[FY] < SelectedFY )
)
VAR ThisFYPerformance = 'Combined Sales'[Sum of NetSalesValue 2]
VAR PreFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[FY] = PreFY
)
)
RETURN
ThisFYPerformance - PreFYPerformance

Hope it works in your scenario.

Best Regards,

Icey

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

9 REPLIES 9  Community Support

Hi @bvanderwatt ,

>> Comparing same period to last year. Monday TY to Monday LY.

So what you want to do is to campare "same week day of same week number of last year" with this day. Right?

If so, try something like this:

same weekday of same weeknum of last year =
VAR CurrentWeekNum_ =
WEEKNUM ( MAX ( 'Combined Sales'[Date] ), 1 )
VAR CurrentWeekDay_ =
WEEKDAY ( MAX ( 'Combined Sales'[Date] ), 1 )
RETURN
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ),
WEEKNUM ( 'Date'[Date], 1 ) = CurrentWeekNum_,
WEEKDAY ( 'Date'[Date], 1 ) = CurrentWeekDay_
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

Thank you so much for your assistance. Much appreciated.

My financial year starts 1 March.

Would it be better to manually add "trading days" which will exclude weekends?

Then I could compare trading days against each other for each financial year.

I am trying to achieve the MTD performance comparison to prior year.   Community Support

hI @bvanderwatt ,

Try this:

VAR StartDate_ =
SWITCH (
TRUE (),
[Month] < 3, DATE ( [Year] - 1, 3, 1 ),
DATE ( [Year], 3, 1 )
)
VAR Weekends_ =
COUNTROWS (
FILTER (
CALENDAR ( StartDate_, [Date] ),
[Date] <= EARLIER ( [Date] )
&& WEEKDAY ( [Date], 1 ) IN { 1, 7 }
)
)
RETURN
IF (
WEEKDAY ( [Date], 1 ) IN { 1, 7 },
BLANK (),
DATEDIFF ( StartDate_, [Date], DAY ) + 1 - Weekends_
) Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

WOW, you are amazing. Thank you so much for helping me. This worked great. You are a GENIUS!

However, how would I write the measure to calculate the performance from the start of the current month (ie. trading day 176; 1 Nov 2021) up until yesterday (ie. trading day 187; 16 Nov 2021) and compare this to the prior year
(ie. trading day 176; 2 Nov 2020) up until yesterday (ie. trading day 187; 17 Nov 2020)  Community Support

Hi @bvanderwatt ,

Is this what you want?

Measure =
VAR ThisFY =
CALCULATE ( MAX ( 'Calendar'[FY] ), 'Calendar'[Date] = TODAY () )
VAR PreFY =
CALCULATE ( MAX ( 'Calendar'[FY] ), 'Calendar'[FY] < ThisFY )
VAR FirstDay_ThisMonth =
EOMONTH ( TODAY (), -1 ) + 1
VAR Yesterday_ =
TODAY () - 1
CALCULATE (
'Calendar'[Date] = FirstDay_ThisMonth
)
CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
)
VAR PreFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
'Calendar'[FY] = PreFY,
)
RETURN
ThisFYPerformance - PreFYPerformance

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

WOW, you are so clever!

I think you've almost nailed it. 🙂

"ThisFYPerformance" needs to be limited to the current month. If I only filter on the month name, I get the below values.

"PrevFYPerformance" is correct, but the "ThisFYPerformance" is pulling through incorrectly. However, when I amend my slicer day to "Nov-2021", "ThisFYPerformane" is correct but my "PrevFYPerformance" is then empty as per below. Do you think I need to amend "VAR FirstDay_ThisMonth" to the first trading day of the current month?

Again, thank you so much for helping me. You are an absolute genius!  Community Support

Hi @bvanderwatt ,

Measure =
VAR ThisFY =
CALCULATE ( MAX ( 'Calendar'[FY] ), 'Calendar'[Date] = TODAY () )
VAR PreFY =
CALCULATE (
MAX ( 'Calendar'[FY] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[FY] < ThisFY )  ---------modified
)
VAR FirstDay_ThisMonth =
EOMONTH ( TODAY (), -1 ) + 1
VAR Yesterday_ =
TODAY () - 1
CALCULATE (
'Calendar'[Date] = FirstDay_ThisMonth
)
CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
'Calendar'[FY] = ThisFY,
)
VAR PreFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
FILTER (
----------------modified
ALL ( 'Calendar' ),
'Calendar'[FY] = PreFY
)
)
RETURN
ThisFYPerformance - PreFYPerformance

If you need to limit to the current month, the slicer is not needed when using this measure.

If you want to dynamically change the filtered month, try this measure:

Measure2 =
VAR SelectedFY =
MAX ( 'Calendar'[FY] )
VAR PreFY =
CALCULATE (
MAX ( 'Calendar'[FY] ),
FILTER ( ALL ( 'Calendar'[FY] ), 'Calendar'[FY] < SelectedFY )
)
VAR ThisFYPerformance = 'Combined Sales'[Sum of NetSalesValue 2]
VAR PreFYPerformance =
CALCULATE (
'Combined Sales'[Sum of NetSalesValue 2],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[FY] = PreFY
)
)
RETURN
ThisFYPerformance - PreFYPerformance

Hope it works in your scenario.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

I would like to track the performance MTD.

However, the MTD formula tracks last year against a full month and I've only completed 10 days of sales this year.

I would like to compare like for like.  Super User

How do you choose the day to compare to? What is the defining rule?  