Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculating a datediff measure amount with a filter

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's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

2 Replies

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

    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

        )

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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