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 want to calculate total sales last month till today. So, the last month sales should show the sales amount till the same date as today of previous month not the entire month.
Order Date | Sales Amount |
12/01/2021 | 2000 |
12/10/2021 | 2200 |
12/18/2021 | 1800 |
12/24/2021 | 1900 |
12/27/2021 | 2100 |
12/30/2021 | 2900 |
If running the measure on 1/24/2022 then it should the sum of sales amount from 12/1/2021 till 12/24/2021. Also, when running it on 2/24/2022 then it should show the sum of sales amount from 1/1/2022 to 1/24/2022
Thanks
@ajve22 , Try like these examples
MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )
LMTD =
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )
In case some date is selected
MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LMTD QTY forced=
var _max = date(year(today()),month(today())-1,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)
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |