GROUPING BY DateDiff Column

DAX DAX Calculations
1

I was initially seeking to identify average days until availability, but the distribution is being distorted so I’m using groups.

Using =IF(OR(BB2 = “”,BA2=""),“null”,DATEDIF(BB2,BA2,“d”)) in Excel before import as Report Avail
Classify Time to make report availabile from Request date as
tbl:
ReportItinery
Groups ItineraryMin ItineraryMax
0 Within 24 HRS 0 1
1 Day 1 2
2 Day 2 3
3 - 7 Days 3 7
7 - 14 Days 7 14
Greater Than 14 Days 14 365
–===
Average of Report Avail average per Date of Report =
AVERAGEX(
KEEPFILTERS(VALUES(‘Sec_tbl’[Date of Report])),
CALCULATE(AVERAGE(‘Sec_tbl’[Report Avail]))
)

–===
Total Docs = count(‘sec_tbl’[DOCUMENT NAME])
Total Documents = count(‘Sec_tbl’[DOCUMENT ID])
Total Report Avail = count(‘Sec_tbl’[Report Avail])

Itinerary Grouping =
CALCULATE( [Total Docs],
FILTER( VALUES( List[Name] ),
COUNTROWS(
FILTER( ‘Report Itinerary’,
[Total Report Avail]>= ‘Report Itinerary’[ItineraryMin] &&
[Total Report Avail] < ‘Report Itinerary’[ItineraryMax] ) ) > 0 ) )

Itinerary GroupingR =
CALCULATE( [Total Report Avail]],
FILTER( VALUES( List[Name] ), ,
COUNTROWS(
FILTER( ‘Report Itinerary’,
[Total Report Avail]>= ‘Report Itinerary’[ItineraryMin] &&
[Total Report Avail] < ‘Report Itinerary’[ItineraryMax] ) ) > 0 ) )

Report Avail Name Itinerary GroupingR Groups
0 Buck Johnson 1 1 Day
0 Chuck Barkley 6 3 - 7 Days
1 James Bond 8 7 - 14 Days
1 Mark Wahlberg 15 Greater Than 14 Days
14 John Wayne 2 2 Days
18 James Bond 1 1 Day

shown as a percentage, I get the expected 3 - 7 Days as the bigger distribution, which was indicated with the Itinerary GroupingR
& Groups when charting stacked bar chart by Q & Y. While the use of Report Avail is a critical filter
for the correct Itinerary GroupingR Groups in the table :

Report Avail Name Itinerary GroupingR Groups
0 Buck Johnson 1 1 Day
0 Chuck Barkley 6 3 - 7 Days
1 James Bond 8 7 - 14 Days
1 Mark Wahlberg 15 Greater Than 14 Days
14 John Wayne 2 2 Days
18 James Bond 1 1 Day

I don’t think I used the correct logic to utilize the Report Avail column correctly to get the Name’s for the correct groups.

Any help would be greatly appreciated.

2

I think the reality here is you need to share some images and also the pbix file with the scenario you are dealing with.

It just too difficult to understand exactly what is happening based on what you have provided so far.

Have you checked out the dynamic grouping material below?

image
81C5FE6E-2489-43FB-A5D0-DA130E47F09A.jpeg2048×863 536 KB

I also recommend taking a look at the banner above your questions which details tips for getting quick solutions.

Thanks
Sam

1 Like
3

Hi @Ackrite55 , we’ve noticed that no response has been received from you since January 29, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!