topic Re: Calculating a datediff measure amount with a filter in DAX Commands and Tips

Calculating a datediff measure amount with a filter

Anonymous — Tue, 22 Nov 2022 23:47:01 GMT

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

Re: Calculating a datediff measure amount with a filter

FreemanZ — Wed, 23 Nov 2022 00:07:09 GMT

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

)

Re: Calculating a datediff measure amount with a filter

Anonymous — Wed, 23 Nov 2022 07:03:33 GMT

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