Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I'm trying to devise a method whereby I can compare MTD versus the same period last year.. but it needs to be the same period in terms of days..
eg: today is the 13th September, MTD would be sales made from Sunday 1st September to Friday 13th September.
but as we have slower trading days on a Sunday, i need to compare against the same period last year which would be Sunday 2nd September to Friday 14th September.
My MTD formula works a treat
=TOTALMTD ( SUM (BookingTable[Total Revenue] ),CallDate[Date] )
But when i attempted a SAMEPERIODLASTYEAR formula, it was doing Saturday 1st September to Thursday 13th September
=CALCULATE ( [NetRevenueMTD], SAMEPERIODLASTYEAR (CallDate[Date]))
I thought i could use the DATEADD formula to move everything on one day, but that didnt work and instead gave me Saturday 1st September to Friday 14th September!
=CALCULATE ( [NetRevenueMTD], SAMEPERIODLASTYEAR (DATEADD(CallDate[Date] ,+1,DAY )))
If it helps, I have a field on my "CallDate" table that shows the equivalent date of last year.
So not sure if this can be used to any effect.
Any help would be great.
Many thanks
Chris
Solved! Go to Solution.
Actually in the second set of formula, I should have added one not subtracted
MTD.
Year 364 MTD = Var _end_date= max('Date'[Date Filer])-364 Var _start_date =minx(ALLSELECTED('Date'),STARTOFMONTH('Date'[Date Filer]))-364 Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date) return _last_year_mtd_val
To Test , You can return _start_date & " and " & _end_date and check dates
Can you try something like this
Year 364 = Var _end_date= max('Date'[Date Filer])-364 Var _start_date =min('Date'[Date Filer])-364 Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date) return _last_year_val
Date here is marked at the date in power bi
Thanks for this..
I applied it into my model in the following way...
=Var _end_date= MAX('CallDate'[Date])-364 Var _start_date =MIN('CallDate'[Date])-364 Var _last_year_val= TOTALMTD ( SUM (BookingTable[Total Revenue] ),CallDate[Date] >= _start_date && (CallDate[Date]) <= _end_date) return _last_year_val
but it still doesnt give me what i need, it seems to be giving me a MTD still with the 1st day of the month last year included.
All being well, the two yellow rows should reconcile.. but as you can see they do, with the exception of day1.
(*Rec - is the difference in Total MTD Revenue for Last Year compared to previous day)
It only seems to be the £115,397 that's the problem, as the other MTD total is incrimenting by the amount I would expect.
Hope that all makes sense.
All being well, the NetRevenueMTDLY figure would match the RevenueLast7DaysLY figure on the 7th September.
For me, your initial formula also working. Just check the date filter at dim or fact
last year day = CALCULATE(min(Sales[Sales Date]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date Filer],-1,DAY))) Last Year Same Week Day Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date Filer],-1,DAY)))
See data comparison
I am confused by this, surely Friday 28th June 2019 equivalent date is Friday 29th June 2018, and therefore the 'Last Year Same Week Day Sales' should be 407 and not 118.
I got Last Year day sales working fine, i just wanted to know if there is a way i can have MTD Sales.
So when I refresh the report on Monday, i have a cell saying MTD sales in X, and the same period last year was Y (the same period being Sun 2nd Sept to Sun 16th Sept - NOT 1st to 15th)
Actually in the second set of formula, I should have added one not subtracted
MTD.
Year 364 MTD = Var _end_date= max('Date'[Date Filer])-364 Var _start_date =minx(ALLSELECTED('Date'),STARTOFMONTH('Date'[Date Filer]))-364 Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date) return _last_year_mtd_val
To Test , You can return _start_date & " and " & _end_date and check dates
Superb, thank you so much!!
@IrishChristof I had solution posted here for the similar question
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |