Forum Discussion

IDepi22's avatar
IDepi22
Frequent Visitor
4 years ago
Solved

Relative Date Filter based on Second Date

Hello,

 

I am trying to filter one date off of another. I need to determine how many people were given both rx1 & rx2 in the same month, using rx2_date as a slicer. Here is some sample data:

 

 

I attempted to create a measure based on another post:

 

Measure = CALCULATE(count(Table[id]), filter(Table, (Table[rx1_date].[Year] && Table[rx1_date].[Month])=(Table[rx2_date].[Year] && Table[rx2_date].[Month])))
 
but I received an error when using it in my visual. What would be the proper way to achieve my goal?
 
Thank you!
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi IDepi22 ,

     

    Please check this measure:

    Measure =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        YEAR ( 'Table'[rx1_date] ) = YEAR ( 'Table'[rx2_date] )
            && MONTH ( 'Table'[rx1_date] ) = MONTH ( 'Table'[rx2_date] )
    )
)

     

    Best Regards,

    Jay 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi IDepi22 ,

     

    Please check this measure:

    Measure =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        YEAR ( 'Table'[rx1_date] ) = YEAR ( 'Table'[rx2_date] )
            && MONTH ( 'Table'[rx1_date] ) = MONTH ( 'Table'[rx2_date] )
    )
)

     

    Best Regards,

    Jay 

    • IDepi22's avatar
      IDepi22
      Frequent Visitor
      4 years ago

      Thank you for your help! This worked, but I made one adjustment. When I included "ALLSELECTED" in front of the table name, I got the same number for each age and gender breakdown. When I removed that, I was able to break down the total by age and gender.