Find Date Of Last Result And Then Difference In A Data Table - Power BI

DAX DAX Calculations
1

Hi, I am just getting started with DAX and have a question as to how I would group my results.

Sample Table:
image

I need to be able to calculate the average number of days between inspections completed for each fleet number

Can anyone offer any assistance as to how I would go about this so that I would end up with something along the lines of:

Fleet Number Average Days Between Inspections Completed
6002 33
6003 63

Regards
Mark.

1 Like
2

Hi
Most of the work can/should be done in the query editor. Duplicate your datecolumns assuming you wish to keep these, change type (of the duplicated columns) into whole number, now you can subtract the date columns. Then group by Fleetnumber and apply the required aggregation.

Paul

Enterprise%20DNA%20Expert%20-%20Small

3

Hi Mark,

Yes Paul is right, that’s how you would do it.

Then all you would need to use with DAX once you have the difference in days within a column is use the AVERAGE function.

Should be as simple as that.

Let us know how you go.

Chrs

4

Hi,
I might be wrong but Mark may be looking for the Average Completion Days for each fleet by calculating the difference of days between preceding rows, equivalent to using LAG in TSQL. I couldn’t come up with an easy answer for that than this. Perhaps, Sam will come up with a smatter option.

Avg Completion Days =
AVERAGEX (
    ADDCOLUMNS (
        Sheet1,
        "DiffBetweenCompetionDates", DATEDIFF (
            CALCULATE (
                MAX ( Sheet1[InspectionCompleted] ),
                FILTER (
                    Sheet1,
                    Sheet1[InspectionCompleted] < EARLIER ( Sheet1[InspectionCompleted], 1 )
                        && Sheet1[FleetNumber] = EARLIER ( Sheet1[FleetNumber], 1 )
                )
            ),
            Sheet1[InspectionCompleted],
            DAY
        )
    ),
    [DiffBetweenCompetionDates]
)
5

Hi,
Many thanks for the responses and sorry for not getting back sooner.
Thanura is correct, I am looking to calculate the difference in days between inspections using the DayeInspectionCompleted column and comparing this to the DateInspectionCompleted in the previous record.
Having calculated these days, I then need to work out the average days for each fleet number.
I will give the above suggestion a go and let you know how it goes.
Really appreciate the help.
Regards Mark.

6

Hi,
I have used the above code in a measure but it does not return any results.
The code when applied to my data looks like this:

Avg Completion Days = 
AVERAGEX (
ADDCOLUMNS (
InspectionsDueCompleted,
"DiffBetweenCompetionDates", DATEDIFF (
CALCULATE (
MAX ( InspectionsDueCompleted[DateInspectionCompleted]),
FILTER (
InspectionsDueCompleted,
InspectionsDueCompleted[DateInspectionCompleted] < EARLIER ( InspectionsDueCompleted[DateInspectionCompleted], 1 )
&& InspectionsDueCompleted[FleetNumber] = EARLIER ( InspectionsDueCompleted[FleetNumber], 1 )
)
),
InspectionsDueCompleted[DateInspectionCompleted].[Date],
DAY
)
),
[DiffBetweenCompetionDates]
)

Could I trouble you to explain each process.

I am just starting out with DAX and am still coming to grips with it’s syntax.

Kind regards
Mark.

7

Hi Mark,

First quick thing…really recommend working on the way your formulas are set out. It may seem like a small thing but will really pay dividends in the long term. DAX can get quite complicated and structuring things well will speed up your development, I’m 100% confident in this.

See below for more ideas

Whenever I look at problems like this one I always try to work out the simplest way to solve it first and weigh up is it optimal enough versus any alternative.

Something there may be more theoretically optimal ways to solve it but I will always go to the simplest if I feel it is good enough.

I understand better now the main issue here so this is how I would solve it.

First I would try to get a calculated column the shows the days difference between all the inspections.
(Let’s start here and then see if it’s easy enough to move it into a measure)

Last Search Date = 
VAR CurrentSearchDate = 'Cases Data'[Case Search Date]
VAR CurrentProduct = 'Cases Data'[Product]

RETURN
CALCULATE( MAX( 'Cases Data'[Case Search Date] ),
        FILTER( 'Cases Data', 'Cases Data'[Case Search Date] < CurrentSearchDate ),
            FILTER( 'Cases Data', 'Cases Data'[Product] = CurrentProduct ) )

You see with this formula I am grabbing the last date for every product in this case.

image
image.png698Ă—523 36.9 KB

Then you can get to here very easily

Search Date Difference = 
IF( ISBLANK( 'Cases Data'[Last Search Date] ),
    BLANK(),
        'Cases Data'[Case Search Date] - 'Cases Data'[Last Search Date] )

image
image.png798Ă—614 40.8 KB

Then once you have this column all you would need it something as simple as this

Average Difference = 
AVERAGEX( 'Cases Data',
    'Cases Data'[Search Date Difference] )

image
image.png587Ă—574 17.7 KB

This should do it for you I believe.

See how you go with these ideas

Chrs

8

Hi Sam,

A massive thank You from me.
This works a treat.
Many thanks for taking the time to explain what is happening at each step.
It’s one thing to get an answer to a problem and another to understand how the process works.

Have taken on board your comments concerning syntax and I can see how much easier the DAX commands are to understand when laid out in this way.

Kind regards
Mark.

9

That’s great, chrs