Filtering sales until today() is working, but not by equivalent max(date)

[Anonymous]

Hi all, I would like to filter sales of the current year. 

Following function perfectly does the job (I know there are more elegant ways to do this 😉 😞 

sales_year = CALCULATE(SUM('sales_table'[sales]);

FILTER('sales_table';YEAR('sales_table'[date]) =YEAR( TODAY () ) ))

If I replace I the bold part with YEAR(MAX('sales_table'[date])) there is no more filtering.

I am confused as both functions return the same output in datetime format (as does LASTDATE, LASTNONBLANK).

Anybody knows what is going on here?

Thanks a lot!

[V-pazhen-msft]

@Anonymous 

Basically, Lastdate() and lastnoblank() for date/time columns return a table containing a single column and single row with a date value. Max() returns a value, although both return the same date. I think this the difference causing the difference when using them in filters.

https://docs.microsoft.com/en-us/dax/max-function-dax
https://docs.microsoft.com/en-us/dax/lastdate-function-dax
https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax

Best,
Paul

[Anonymous]

@V-pazhen-msft thanks for your feedback!

But how come that neither a datatime-value nor 1r1c-table works? today() also returns a datatime-value?

[amitchandak · Accepted solution]

Try datesytd or totalytd with calendar table. try

sales_year = 
var _year = YEAR(maxx('sales_table','sales_table'[date]))
CALCULATE(SUM('sales_table'[sales]);
FILTER('sales_table';YEAR('sales_table'[date]) =_year  ) ))

Or 

sales_year = 
var _year = YEAR(maxx('sales_table','sales_table'[date]))
CALCULATE(SUM('sales_table'[sales]);all('sales_table'[date]);
FILTER('sales_table';YEAR('sales_table'[date]) =_year  ) ))

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/

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Try

[Anonymous]

Perfect thanks, I added a return statement and this does the job! Thanks again!

sales_year = 
var _year = YEAR(maxx('sales_table','sales_table'[date]))
RETURN
CALCULATE(SUM('sales_table'[sales]);all('sales_table'[date]);
FILTER('sales_table';YEAR('sales_table'[date]) =_year  ) ))