cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvanderwatt
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
Icey
Community Support
Community Support

Hi @bvanderwatt ,

 

Sorry, some conditions are missing. How about this one?

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
VAR trading_day_FirstDay_ThisMonth =
    CALCULATE (
        MAX ( 'Calendar'[Trading Days] ),
        'Calendar'[Date] = FirstDay_ThisMonth
    )
VAR trading_day_Yesterday_ =
    CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        'Calendar'[FY] = ThisFY,
        ------------------------added
        'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth,
        'Calendar'[Trading Days] <= trading_day_Yesterday_
    )
VAR PreFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        FILTER (
            ----------------modified
            ALL ( 'Calendar' ),
            'Calendar'[FY] = PreFY
                && 'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth
                && 'Calendar'[Trading Days] <= trading_day_Yesterday_
        )
    )
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 trading_day_FirstDay_SelectedMonth =
    MIN ( 'Calendar'[Trading Days] )
VAR trading_day_LastDay_SelectedMonth =
    MAX ( 'Calendar'[Trading Days] )
VAR ThisFYPerformance = 'Combined Sales'[Sum of NetSalesValue 2]
VAR PreFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[FY] = PreFY
                && 'Calendar'[Trading Days] >= trading_day_FirstDay_SelectedMonth
                && 'Calendar'[Trading Days] <= trading_day_LastDay_SelectedMonth
        )
    )
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.

View solution in original post

9 REPLIES 9
Icey
Community Support
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.

bvanderwatt
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. 

 

What's your thoughts? 

 

bvanderwatt_0-1636789294624.png

 

 

Icey
Community Support
Community Support

hI @bvanderwatt ,

 

Try this:

Trading Days = 
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_
    )

FY - DAY.png

 

 

Best Regards,

Icey

 

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

bvanderwatt
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)

Icey
Community Support
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
VAR trading_day_FirstDay_ThisMonth =
    CALCULATE (
        MAX ( 'Calendar'[Trading Days] ),
        'Calendar'[Date] = FirstDay_ThisMonth
    )
VAR trading_day_Yesterday_ =
    CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth,
        'Calendar'[Trading Days] <= trading_day_Yesterday_
    )
VAR PreFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        'Calendar'[FY] = PreFY,
        'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth,
        'Calendar'[Trading Days] <= trading_day_Yesterday_
    )
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.

bvanderwatt
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.

bvanderwatt_0-1637219923027.png

 

However, when I amend my slicer day to "Nov-2021", "ThisFYPerformane" is correct but my "PrevFYPerformance" is then empty as per below. 

bvanderwatt_1-1637219966117.png

 


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!

 

Icey
Community Support
Community Support

Hi @bvanderwatt ,

 

Sorry, some conditions are missing. How about this one?

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
VAR trading_day_FirstDay_ThisMonth =
    CALCULATE (
        MAX ( 'Calendar'[Trading Days] ),
        'Calendar'[Date] = FirstDay_ThisMonth
    )
VAR trading_day_Yesterday_ =
    CALCULATE ( MAX ( 'Calendar'[Trading Days] ), 'Calendar'[Date] = Yesterday_ )
VAR ThisFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        'Calendar'[FY] = ThisFY,
        ------------------------added
        'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth,
        'Calendar'[Trading Days] <= trading_day_Yesterday_
    )
VAR PreFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        FILTER (
            ----------------modified
            ALL ( 'Calendar' ),
            'Calendar'[FY] = PreFY
                && 'Calendar'[Trading Days] >= trading_day_FirstDay_ThisMonth
                && 'Calendar'[Trading Days] <= trading_day_Yesterday_
        )
    )
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 trading_day_FirstDay_SelectedMonth =
    MIN ( 'Calendar'[Trading Days] )
VAR trading_day_LastDay_SelectedMonth =
    MAX ( 'Calendar'[Trading Days] )
VAR ThisFYPerformance = 'Combined Sales'[Sum of NetSalesValue 2]
VAR PreFYPerformance =
    CALCULATE (
        'Combined Sales'[Sum of NetSalesValue 2],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[FY] = PreFY
                && 'Calendar'[Trading Days] >= trading_day_FirstDay_SelectedMonth
                && 'Calendar'[Trading Days] <= trading_day_LastDay_SelectedMonth
        )
    )
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.

View solution in original post

bvanderwatt
Frequent Visitor

Good Day. Thank you for your reply. 

 

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. 

AlexisOlson
Super User
Super User

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors