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 need help to get the right formula for my dashboard
As you can see on my screenshot, I want to see the MTD from last year.
I managed to do it but the total in my table is not the right one.
The total is the total of the month, while I would like the total of the month until today's date minus 1 year
To obtain the measurement Invoiced Sum YEAR-1 I used the following formula :
Solved! Go to Solution.
@LeroyPaul , When use month it start getting month end date, so we need force
LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
or
LYMTD =
var _min = eomonth(today(),-12)+1
var _max1 = today()
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )
I probably misspoke
I will illustrate with an example
I would like to calculate last year's MTD for the same day
For example, today is the 22/03/2022,
I would like to calculate the MTD for the month of March 2021 until 22/03/2021 (Today's date minus 1 year)
This is what I managed to do in the screenshot but the total should be 22,521 instead of having the total of the whole month 27,206
@LeroyPaul , When use month it start getting month end date, so we need force
LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
or
LYMTD =
var _min = eomonth(today(),-12)+1
var _max1 = today()
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )
Thanks a lot @amitchandak , the following formula was good 😊 :
OR
CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
@LeroyPaul , for last year same month MTD you should try measure like
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last month MTD
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |