Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Dear all,
I´m trying a "simple" measure to calculate the cumulative values from the beginning of the year until a month from the most recent date.
For example, In FactSales table I have column OrderDate where the last date is 2020.07.30
The formula below works:
Sum Of Sales Amount YTD =
VAR max_month = 7
RETURN
TOTALYTD(
[Sum Of SalesAmount],
DimDate[Date],
MONTH(FactSales[OrderDate]) <= max_month
)
but this formula does not work:
Sum Of Sales Amount YTD =
VAR max_month = MONTH(MAX(FactSales[OrderDate]))
RETURN
TOTALYTD(
[Sum Of SalesAmount],
DimDate[Date],
MONTH(FactSales[OrderDate]) <= max_month
)
Any help?
Solved! Go to Solution.
@Lukasz004 , You have try like example
YTD=
var _max = maxx(allselected('order'),'order'[order date])
var _min = date(year(_max ),1,1)
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
or
YTD=
var _max = maxx(allselected('order'),'order'[order date])
var _min = date(year(_max ),1,1)
return
CALCULATE(sum('order'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
@Lukasz004 , You have try like example
YTD=
var _max = maxx(allselected('order'),'order'[order date])
var _min = date(year(_max ),1,1)
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
or
YTD=
var _max = maxx(allselected('order'),'order'[order date])
var _min = date(year(_max ),1,1)
return
CALCULATE(sum('order'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
I changed my variable:
VAR max_month = MONTH(MAX(FactSales[OrderDate]))
to
VAR max_month = MONTH(MAXX(ALL(FactSales), FactSales[OrderDate]))
and the formula works fine 😉
Thanks for your help @amitchandak
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
93 | |
58 | |
44 | |
35 | |
34 |