New Enterprise DNA Initiatives

SUMX On DateDiff?

DAX Calculations
#1

Hello,

I have a measure like this:

Days Change Previous Report = 
VAR vThisCompDate =
    MIN ( Backlog[End Date (Ops)] )
VAR vThisReportDate =
    MIN ( Backlog[Report Date] )
VAR vPrevCompDate =
    CALCULATE (
        LASTNONBLANKVALUE (
            Backlog[Report Date],
            MIN ( Backlog[End Date (Ops)] )
        ),
        ALLEXCEPT (
            Backlog,
            Backlog[Project],
            Backlog[Customer],
            Backlog[Job Site]
        ),
        Backlog[Report Date] < vThisReportDate
    )
VAR DaysDiff = DATEDIFF (
        vPrevCompDate,
        vThisCompDate,
        DAY
    )
RETURN
DaysDiff

However, it doesn’t sum correctly the days of difference, please see below screen. Does anyone know how to fix it?

Comm
Comm920Ă—219 52.4 KB

Here is the pbix file, I have created a new “fake” dataset. However, it seems that the days are not added at all now.

My goal would be to see the total days of difference, which will help me to understand what was the total days difference by customer (for example).

thank you,
Marek

Pbi_dset.pbix (85.2 KB)

#2

Hello @marek.regulski,

Thank You for posting your query onto the Forum.

Can you please upload the working of your PBIX file for the reference? So that the members of our forum can assist you in a better manner and provide you the results more efficiently.

Thanks and Warm Regards,
Harsh

1 Like
#3

Edited- thank you.

#4

Hello @marek.regulski,

Thank You for providing the working PBIX file.

Well I just wrote one more small formula in order to have the grand totals. Below is the formula provided for the reference -

Days Change Previous Report - Harsh = 
SUMX(
    SUMMARIZE(
        Pbi , 
        Pbi[Project No.] , 
        Pbi[Job Site] , 
        Pbi[Source.Name] , 
        Pbi[End Date (Ops)] ) , 
    [Days Change Previous Report]
)

Resultant Output
Resultant Output916Ă—454 20.8 KB

I’m also providing a link below where one of our expert @Greg had put up a great post about “How To Fix The Totals”.

As well as also attaching the working of my PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Pbi_dset - Harsh.pbix (85.3 KB)

1 Like
#5

It works! :slight_smile:

Thank you so much for such a detailed answer and further materials. This is great.

#6

Hello @marek.regulski,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help you and you found it useful. :+1:

Thanks and Warm Regards,
Harsh