Calculating a datediff measure amount with a filter

[avawhittington]

Hey, I have a measure that takes the created date of a case and finds the difference to todays date. 

MaxDateDiff = DATEDIFF(max('Cases'[CreateDate2]), TODAY(),day)

It brings back exactly what I want... When I put it into a table I can see the partner and how far back their most recent case date was (Each partner has multiple cases). I grab the partner name from a table 'Partner'

Next, I put a filter on the table using the filter pane to only show the partner name and its difference date if it is between 90 and 180 days. The table does this as well.

Where my problem is: I want to use this in a card. I want to show the amount of partners that have their difference date 90 - 180. When I put the measure in a card it only shows "1"

I'm trying to show inactivity on parters

[Anonymous · Accepted solution]

Hi @avawhittington ,

According to your description, it seems it involves two tables: 'Cases' and 'Partner' and you want to get the count of partners who have the cases with datediff(from case created date to today) is 90~180 days. Am I right? If yes,you can follow the steps below to get it. Please find the details in the attachment.

1. Create two measures as below

MaxDateDiff = 
VAR _selpartner =
    SELECTEDVALUE ( 'Cases'[Partner ID] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Cases'[CreateDate2] ),
        FILTER ( ALLSELECTED ( 'Cases' ), 'Cases'[Partner ID] = _selpartner )
    )
VAR _datediff =
    DATEDIFF ( _maxdate, TODAY (), DAY )
RETURN
    _datediff

Count of partner = 
CALCULATE (
    DISTINCTCOUNT ( 'Cases'[Partner ID] ),
    FILTER ( 'Cases', [MaxDateDiff] >= 90 && [MaxDateDiff] <= 180 )
)

2. Create a card visual

Best Regards

[FreemanZ]

it seems the Card visual is unexpectedly filtered by other contexts, try to plot with a new measure with the code below:

CardMeasure=

CALCULATE

   SUM(TableName[Amount]),

   FILTER(

       ALL(TableName),

        [MaxDateDiff]>=90 

          && [MaxDateDiff]<=180

    )

)