Forum Discussion

Velvetine27's avatar
Velvetine27
Icon for Helper I rankHelper I
3 years ago
Solved

Multiple selection filters not working

Hi all,

 

I wrote a DAX to display data up till the selected month like this : 

 

%CFR=
VAR selectedYear =
    SELECTEDVALUE ( 'DATE'[Date].[Year] )
VAR selectedMon =
    SELECTEDVALUE ( 'DATE'[Date].[MonthNo] )
VAR start_ =
    DATE ( selectedYear, 1, 1 )
VAR end_ =
    DATE ( selectedYear, selectedMon + 1, 1 ) - 1

RETURN
CALCULATE(([DO] / [SO]),FILTER ( 'TABLE', 'TABLE'[date] >= start_ && 'TABLE'[date] <= end_ ))
 
 
 
Now the issue is that in my visuals if the user selects more than one month, this doesn't work. Do you guys have any suggestions how can I keep the functionality of current DAX while allowing users to select multiple months and it will show data up till the last selected month. So if users choose all months, it should display the entire year of data. Any suggestions appreciated. 
 
Thanks !
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Velvetine27 

     try like:

    %CFR=
    VAR selectedYear =
        YEAR(MAX('DATE'[Date]))
    VAR selectedMon =
        MONTH(MAX('DATE'[Date]))
    VAR start_ =
        DATE ( selectedYear, 1, 1 )
    VAR end_ =
        DATE ( selectedYear, selectedMon + 1, 1 ) - 1
    RETURN
    CALCULATE(([DO] / [SO]),FILTER ( 'TABLE', 'TABLE'[date] >= start_ && 'TABLE'[date] <= end_ ))

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Velvetine27 

     try like:

    %CFR=
    VAR selectedYear =
        YEAR(MAX('DATE'[Date]))
    VAR selectedMon =
        MONTH(MAX('DATE'[Date]))
    VAR start_ =
        DATE ( selectedYear, 1, 1 )
    VAR end_ =
        DATE ( selectedYear, selectedMon + 1, 1 ) - 1
    RETURN
    CALCULATE(([DO] / [SO]),FILTER ( 'TABLE', 'TABLE'[date] >= start_ && 'TABLE'[date] <= end_ ))
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Velvetine27 ,

    I created some data:

    Table:

    Date:

     

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _selectyear=SELECTEDVALUE('Date'[Year])
var _selectmonth=MAXX(ALLSELECTED('Date'),[Month])
var _start=DATE(_selectyear,1,1)
var _end=EOMONTH(DATE(_selectyear,_selectmonth,1),0)
return
IF(
    MAX('Table'[date]) >=_start && MAX('Table'[date])<=_end,1,0)
    Measure =
SUMX(ALLSELECTED('Table'),[DO/SO])

    2. Result:

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly