Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax - Date Filter

Hello people,

 

I have two measures for a date table, but for different periods:

 


Accounting period (mm/21/yyyy  to  mm+1/20/yyyy)
Other Periods


Example:


MonthAccount =  8 (06/21/2020 to 07/20/2020)

MonthAccount =  9  (07/21/2020 to 08/20/2020)

MonthAccount = 10 (08/21/2020 to 09/20/2020)

 

I would like to make a measure that identifies the date period the user has filtered in order to choose the appropriate measure.

 

If the user: Filter dates within the accounting period (mm / 21/2020 to mm + 1/20/2020)
    - measure 1
else 
    - measure 2

 

In short: if the filtered period has only one value for MonthAccount, choose measure 1, if not measure 2.

 

Is it possible to do that?

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi Anonymous ,

     

    Please try this:

    Measure =
VAR CountMonthAccount_ =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[MonthAccount] ),
        FILTER (
            'Table',
            'Table'[Date] >= MIN ( 'Date'[Date] )
                && 'Table'[Date] <= MAX ( 'Date'[Date] )
        )
    )
RETURN
    IF ( CountMonthAccount_ = 1, [Measure 1], [Measure 2] )

    Measure 1 = 1
Measure 2 = 0

     

     

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Please try this:

    Measure =
VAR CountMonthAccount_ =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[MonthAccount] ),
        FILTER (
            'Table',
            'Table'[Date] >= MIN ( 'Date'[Date] )
                && 'Table'[Date] <= MAX ( 'Date'[Date] )
        )
    )
RETURN
    IF ( CountMonthAccount_ = 1, [Measure 1], [Measure 2] )

    Measure 1 = 1
Measure 2 = 0