Forum Discussion
Filter not affect measure
Hi ,
I want to create a measure to calculate the Y-1 value based on the selected Year.
- Anonymous5 years ago
Hi Icey amitchandak ,
Thank you for your help.
I've fixed it by allexcept function.
CALCULATE(SUM('Table'[Volumns]),PREVIOUSYEAR('Table'[Date]),'Table'[Type] = "type1",ALLEXCEPT('Table','Table'[Category]))
3 Replies
- amitchandakSuper User
Anonymous , Do you want year on year. If you have date use time intelligence with date table. If not then have year table
With Date or Year table
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))with date table
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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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"))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
Appreciate your Kudos. - IceyCommunity Support
Hi Anonymous ,
Try this:
Measure 2 = CALCULATE ( SUM ( 'Table'[Volumns] ), FILTER ( ALLSELECTED( 'Table' ), YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Calendar'[year] ) - 1 && 'Table'[Type] = "type1" ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Hi Icey amitchandak ,
Thank you for your help.
I've fixed it by allexcept function.
CALCULATE(SUM('Table'[Volumns]),PREVIOUSYEAR('Table'[Date]),'Table'[Type] = "type1",ALLEXCEPT('Table','Table'[Category]))