Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
Solved! Go to 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.
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?
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_
)
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.
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!
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.
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.
How do you choose the day to compare to? What is the defining rule?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |