Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bvanderwatt
Helper III
Helper III

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

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.

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

 

 

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.

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)

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.

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!

 

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.

bvanderwatt
Helper III
Helper III

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.