topic Re: Calculate "Longest Period" to complete a trend chart with some textual insights in DAX Commands and Tips

Calculate "Longest Period" to complete a trend chart with some textual insights

pouletjaune — Wed, 25 Oct 2023 08:58:34 GMT

Dear PBI experts,

I have a challenge that I simplified as per below screenshot (and here's the gantt_chart_periods.pbix as well as the source excel file gantt_sample.xlsx)

So: let's imagine you have a trend chart of "number of tickets per day", and we want to give 2 insights to our end-users : the "Longest Period with Ticket" and "Longest Period without Ticket" : see below screenshot.

From my understanding, this calculation cannot be done at the dataset level, as it should be dynamically impacted by the filters (for example to limit the scope of data by the tickets' priority, and/or country).

So the question is; how, in attached .pbix, we could dynamically create the 2 scorecards I've hardcoded (static texts for now) at the bottom of the chart, and indicate to our users these "Longest Periods with/without tickets"

The objective is to display the start date, the end date and the duration (in days) of the 2 longest periods (with and without ticket)

Many many thanks in advance to share your ideas / hints / advices on how to move on this requirement !

Re: Calculate "Longest Period" to complete a trend chart with some textual insights

Jihwan_Kim — Wed, 25 Oct 2023 02:44:27 GMT

Hi,

I am not 100% sure if it is seen only by myself, but I see the below when I click the link.

Re: Calculate "Longest Period" to complete a trend chart with some textual insights

pouletjaune — Wed, 25 Oct 2023 06:50:48 GMT

Hi Jihwan_Kim and thanks for your reply. Can you retry now ? My bad the file was restricted. I've now opened the file to anyone.

Re: Calculate "Longest Period" to complete a trend chart with some textual insights

Jihwan_Kim — Wed, 25 Oct 2023 18:38:49 GMT

Hi,

Please check the below picture and the attached pbix file.

Longest period with tickets: = VAR _condition = ADDCOLUMNS ( ALLSELECTED ( 'Calendar Table'[Calendar Date] ), "@condition", IF ( [#Tickets] + 0 = 0, 1, 0 ) ) VAR _partition = ADDCOLUMNS ( _condition, "@partition", SUMX ( FILTER ( _condition, 'Calendar Table'[Calendar Date] <= EARLIER ( 'Calendar Table'[Calendar Date] ) ), [@condition] ) ) VAR _partitioncount = GROUPBY ( _partition, [@partition], "@count", SUMX ( CURRENTGROUP (), 1 ) ) VAR _maxpartitioncount = MAXX ( _partitioncount, [@count] ) VAR _maxpartitionnumber = MAXX ( FILTER ( _partitioncount, [@count] = _maxpartitioncount ), [@partition] ) VAR _period = FILTER ( _partition, [@partition] = _maxpartitionnumber ) RETURN COUNTROWS ( _period ) - 1 & " days (from " & MINX ( _period, 'Calendar Table'[Calendar Date] ) + 1 & " to " & MAXX ( _period, 'Calendar Table'[Calendar Date] ) & ")"

Longest period without tickets: = VAR _condition = ADDCOLUMNS ( ALLSELECTED ( 'Calendar Table'[Calendar Date] ), "@condition", IF ( [#Tickets] + 0 <> 0, 1, 0 ) ) VAR _partition = ADDCOLUMNS ( _condition, "@partition", SUMX ( FILTER ( _condition, 'Calendar Table'[Calendar Date] <= EARLIER ( 'Calendar Table'[Calendar Date] ) ), [@condition] ) ) VAR _partitioncount = GROUPBY ( _partition, [@partition], "@count", SUMX ( CURRENTGROUP (), 1 ) ) VAR _maxpartitioncount = MAXX ( _partitioncount, [@count] ) VAR _maxpartitionnumber = MAXX ( FILTER ( _partitioncount, [@count] = _maxpartitioncount ), [@partition] ) VAR _period = FILTER ( _partition, [@partition] = _maxpartitionnumber ) RETURN COUNTROWS ( _period ) - 1 & " days (from " & MINX ( _period, 'Calendar Table'[Calendar Date] ) + 1 & " to " & MAXX ( _period, 'Calendar Table'[Calendar Date] ) & ")"

Re: Calculate "Longest Period" to complete a trend chart with some textual insights

pouletjaune — Wed, 25 Oct 2023 22:36:13 GMT

You rock !!! 😀 That is working like a charm ! Can't thank you enough for this. You made my day. And not only this is working perfectly, but looking at your solution and code, I learnt some nice DAX features here 👍

Again, thanks a lot Jihwan_Kim !

Have a nice evening / day.