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 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 😉 😞
Solved! Go to 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
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
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
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 ) ))
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |