Relative Date Filter based on Second Date

[IDepi22]

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!

[Anonymous · Accepted solution]

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]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.