Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KevinW_SDP
Helper I
Helper I

ALL (Column) behavior issue

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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/

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Please, before you start creating DAX and models which are completely wrong and unmanageable, try to watch these first and then adhere to Best Practices. It'll help you stay sane.

https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

Best
D
amitchandak
Super User
Super User

@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
    )
Anonymous
Not applicable

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

vivran22
Community Champion
Community Champion

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors