Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Filter function not working on DATE table

Hi Everyone,

I have date table, within which there are columns "Year" and "Current Year".
The DAX for Current Year is = 

if(YEAR(Date_Dimension[Date])=Year(TODAY()),"Current Year",Date_Dimension[Year]).
Both Year and Current Year are in Text Format.(I cannot change it).
I will be using Current year in a slicer.
I have created a measure, in which I need to calculate for previous year which is not working for me. Can anyone of guys help a brother out?
 
Selectedyear =
VAR SelectedYear =
    IF(
        ISFILTERED(Date_Dimension[Current Year]) &&
        SELECTEDVALUE(Date_Dimension[Current Year]) = "Current Year",
        VALUE(MAX(Date_Dimension[Year])) - 1,
        VALUE(SELECTEDVALUE(Date_Dimension[Year])) - 1
    )
VAR Formatter = FORMAT(SelectedYear, "####")

RETURN
Formatter

The measure that I created, 
 
Measure 2 =
CALCULATE(
    SUM('Sales'[Quantity]),
    FILTER( Date_Dimension,
    Date_Dimension[Year] = [Selectedyear]
)
)
Now of I select anything in Slicer, it is just showing blank.
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Try like

     

    Measure 2 =
    CALCULATE(
    SUM('Sales'[Quantity]),
    FILTER(all( Date_Dimension),
    Date_Dimension[Year] = [Selectedyear]
    )
    )

     

     

    If you want to default the year on Current year, You need a column

     

    Year Type = Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )

     

     

    Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ankit,
      Thank you for the reply. I used the ALL function in the measure as well. It is resulting in blank.

       

      Is there any other work around?