Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Nepal101
Helper III
Helper III

Calculating days between dates in same column

Hello Everyone, 
I have a question regarding how to calculate the column or Measure that gives me days between dates

DateClientNamesalespersonDays BetweenRunning total days or sum days
10/26/2023123null0 (because the salesperson is not assigned)0
10/27/2023123A11
10/30/2023123A34
11/6/2023123A711
11/7/20231213a112

from the same column and on top of that I need to have the sum of days for each row. 
Is there a way to calculate the Total days or sum days and days between
I hope someone can help me get this result. 
Thank you in advance 


8 REPLIES 8
danextian
Super User
Super User

Hi @Nepal101 ,

Assuming that the days difference is to be calculated by client name:

Days Diff = 
VAR __PREV =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )
        )
    )
RETURN
    DATEDIFF ( __PREV, 'Table'[Date], DAY ) + 0

Otherwise, remove this line:

&& 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )

danextian_0-1709162027049.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

I tried to use the column as you have stated. I think there is a logic missing regarding the salesperson so if the sales person is not assigned then the day should not be count. Can we create that column accordingly. 

Nepal101_0-1709164382980.png

 

Hi @Nepal101 

 

Use this instead:

Days Diff = 
VAR __PREV =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )
                && 'Table'[salesperson] <> "null" )
        )
    
RETURN
    DATEDIFF ( __PREV, 'Table'[Date], DAY ) + 0

As to the running total, you can either use a calc column or a measure but calc column is not to be aggregated as it will show incorrect sub or grand total. Please see attached pbix for the details.

danextian_0-1709181567460.png

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

At first I created 

test = If('MV[Salesperson]<>Blank(),'MV'[Date],Blank())
MeasureMIN-new = CALCULATE(MIN('MV'[test]),ALLEXCEPT('MV'[Client Name]))
Measure 3 = [MeasureMIN-new]-1 ( so that I can get 1 day when the salesperson is assigned)
Date diff =
var fistdate= [MeasureMIN-new]
var Demote =
CALCULATE(
    MAX('MV'[test]),
    Filter(
        ALLEXCEPT('MV'[Client Name]),
        'MV'[test])))

        Return
       
     If (
        'MV'[test]=fistdate,
        DATEDIFF([Measure 3],'MV'[test],Day),
        DATEdiff(Demote,'MV'[test],DAY))
Nepal101_0-1709228210902.png

 

Is there a way to count the days rolling over meaning for 11/6/2023 =1 on 11/7/2023 it should show 2  and 11/8/2023  it should show 3  and for 11/14/2023 it should show 9 for that row of date. is that possible. I can't upload the file so hope this helps what I have explained. 

Hi @Nepal101 

I would suggest you provided enough information and sample data that covers your use case instead of just  bits and pieces.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Sorry about the confusion, 
I have used what you have provided to me to some extent to get the result. I appreciate your time. 
Thank you 

Hello @danextian 
Thank you so much for your insight. 
I have one more question is there a way to coun the days when the sales person is assigned instead of showing 0days 

Nepal101_0-1709219638703.png

as the sales person was assigned on that paticular day I need to count that day as one to get the correct value. 

Hello @danextian
Thank you so much for your help and time. 
is there a way to calculate the running total for the days as well please. 
Can the days be sum up for each rows ?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.