Filter not affect measure

[Anonymous]

Hi , 

I want to create a measure to calculate the Y-1 value based on the selected Year. 

CALCULATE(

SUM(Table[Volumns]),

FILTER(ALL(Table), YEAR(Table[Date]) = SELECTEDVALUE('Calendar'[year]) - 1 && Table[Type] = "type1")

)

The above measure works well with fixed value and not affected by any filter. 

But when I add one more filter -- allselected(Table[Category]) to this filter , it can't work.

CALCULATE(

SUM(Table[Volumns]),

FILTER(ALL(Table), YEAR(Table[Date]) = SELECTEDVALUE('Calendar'[year]) - 1 && Table[Type] = "type1" && Allselected(Table[Category]))

)

How can I add the silcer filter condition to the formular? 

Thanks!

[Icey]

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.

[Anonymous · Accepted solution]

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])

)

[amitchandak]

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