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.
I am working on some DAX and it is not working as I expect. I tried using some of the DAX functions to do this, but they weren't quite what I was needing. Also, I am doing this in SSAS which seems to have a very slight difference in support for newer DAX commands.
I am working on creating a Prior Year To Date functionality, but the precanned TOTALYTD doesn't display results when certain filters are applied. Instead, I am trying to use the code below. The main problem I am having is I need to return all of the results from the Sales table with filters still there except for on the date fields as I want to apply a custom date filter as shown in the code below.
Sales Amount PYTD:=
RETURN
SUMX(
FILTER(
ALL(Sales[invoiced_date]),
YEAR('Sales'[invoiced_date]) = SELECTEDVALUE('- Date'[YearOrder]) - 1 && MONTH('Sales'[invoiced_date]) <= SELECTEDVALUE('- Date'[MonthOfYearNumber])
),
[sales_amount]
)
I am using ALL(Sales[invoiced_date]) which according to the Microsoft documentation here, this code should "Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters." I interpret that as by using this in the manner I have, it should return all data in that table but remove the filter from the Sales[invoiced_date] column.
The problem is, when I do this, Visual Studio is throwing a "Semantice Error: Column 'sales_amount' cannot be found or may not be used in this expression." Can anyone give me some insight on what I am doing wrong here?
Thank you in advance.
Solved! Go to Solution.
@KevinW_SDP , You can try YTD with following method. Prefer a date calendar do not use Date from fact.
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
@KevinW_SDP , You can try YTD with following method. Prefer a date calendar do not use Date from fact.
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Thank you for the reply. Below is the code that ended up giving me what I am needing.
Sales Amount PYTD:=
var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
RETURN
CALCULATE (
Sum('Sales'[sales_amount]),
DATESYTD(
DATEADD('- Date'[Date], -1, YEAR)),
'- Date'[Date] <=_max
)
Here is the insight you wanted.
First, sorry to say that but this code is flawed. This piece
var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
will be wrong if you stumble upon a leap year. Check it out.
Secondly,
Sales Amount PYTD :=
SUMX (
FILTER (
ALL ( Sales[invoiced_date] ),
YEAR ( 'Sales'[invoiced_date] )
= SELECTEDVALUE ( '- Date'[YearOrder] ) - 1
&& MONTH ( 'Sales'[invoiced_date] )
<= SELECTEDVALUE ( '- Date'[MonthOfYearNumber] )
),
[sales_amount]
)
gives you a semantic error because the table ALL( Sales[invoiced_date] ) has only one column: invoiced_date. And you are iterating over it trying to extract [sales_amount] which is not present in it.
I have a question regarding your measure:
Sales Amount PYTD :=
VAR _max =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
CALCULATE (
SUM ( 'Sales'[sales_amount] ),
DATESYTD ( DATEADD ( '- Date'[Date], -1, YEAR ) ),
-- What is the purpose of this filter?
'- Date'[Date] <= _max
)
What's the purpose of the filter marked with my comment? The one with "... <= _max"? Does it in any way contribute to the calculation? 'Cause I think it does not. Can you please explain? Thanks.
Best
D
Hello @KevinW_SDP ,
Please share sample data with expected results (including the filters you were applying and not getting the right results).
Ideally Time Intelligence functions should solve your problem.
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
Please read my original question. I have not got to the point of publishing this code because it throws an error in Visual Studio as mentioned at the end of the question. The problem is not the results of the query, but the fact that the documentation states one thing and when coding it, it is throwing an error stating it is not being used properly.
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 |