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.
Hello,
I'm trying to create a report with sales this year compare to the same period LY but I would like to block it at the same day.
As example below I would like that to compare 2020 and 2019 in August and to be comparable, 2019 need to be stop at the last day of 2020.
My formula is
Net Sales LY =
CALCULATE(sum('B&M_Database_All'[Net Sales]),
SAMEPERIODLASTYEAR('Dim Date'[Date]))
How I can do that ?
Thanks in advance for your help.
Solved! Go to Solution.
Net Sales LY =
var __latestDateAvailable =
CALCULATE(
MAX( 'B&M_Database_All'[Date] ),
ALL( 'B&M_Database_All' )
)
RETURN
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(
FILTER(
VALUES( 'Dim Date'[Date] ),
'Dim Date'[Date] <= __latestDateAvailable
)
)
)
[Total Sales] should be the very simple measure that just returns the total sales. Something like SUM( FactTable[Sales Amount] ).
Net Sales LY =
var __latestDateAvailable =
CALCULATE(
MAX( 'B&M_Database_All'[Date] ),
ALL( 'B&M_Database_All' )
)
RETURN
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(
FILTER(
VALUES( 'Dim Date'[Date] ),
'Dim Date'[Date] <= __latestDateAvailable
)
)
)
[Total Sales] should be the very simple measure that just returns the total sales. Something like SUM( FactTable[Sales Amount] ).
Thanks, this one is ok and @amitchandak if I add to your formula the code as below, it's ok.
var _max1 = calculate(maxx('B&M_Database_All',[Date - Billing]),All('B&M_Database_All'))
Thank you both for your help.
@Anonymous , hope you formula working fine.
Try like
Net Sales LY=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(sum('B&M_Database_All'[Net Sales]),
SAMEPERIODLASTYEAR('Dim Date'[Date]), 'Dim Date'[Date] <=_max)
make sure date and year in visual table come from Date dim
@amitchandakthanks your proposal function 🙂
Another question, for example in this case, I have not update my database since August 9th, but actual day is August 14th.
Is it possible to block it to the last data fill in 2020 ?
Thanks a lot for your help !
@Anonymous , Take a date from fact
Net Sales LY=
var _max1 =maxx('B&M_Database_All',[date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(sum('B&M_Database_All'[Net Sales]),
SAMEPERIODLASTYEAR('Dim Date'[Date]), 'Dim Date'[Date] <=_max)
Thanks, I have try it, but it generate an error :
Net Sales LY Test 2 =
var _max1 =maxx('B&M_Database_All',[Date - Billing])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(sum('B&M_Database_All'[Net Sales]),
SAMEPERIODLASTYEAR('Dim Date'[Date]),'Dim Date'[Date]<=_max)
On total level, it's ok, but when I want to check day by day or filter on a specific month it generate an error.
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 |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |