Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter not affect measure

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!
  • Anonymous's avatar
    Anonymous
    5 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

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

     

  • Icey's avatar
    Icey
    Community 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.

    • Anonymous's avatar
      Anonymous
      Not 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])
      )