cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Count Difference between dates broken out by month

Is there a way to calculate the difference between two dates but break them out by months?

 

My table example is:

IDDate StartDate EndDuration
12312/11/201816/11/20185
12302/01/201907/01/20196
100303/06/201907/08/201966

 

This count works fine for short dates within the same month but I'm hoping to get a month count between the start and end dates?

 

So I'm hoping to have some sort of breakout/ split to show:

IDJuneJulyAugust
100328317

 

Thanks.

1 ACCEPTED SOLUTION
Super User II
Super User II

@Niiru1  firstly, you need create a date table, which have no relationship with your fact table. then try this code

DaysCount :=
SUMX (
    Table1,
    VAR sd = Table1[Date Start]
    VAR ed = Table1[Date End]
    RETURN
        CALCULATE (
            COUNT ( 'Calendar'[Date] ),
            KEEPFILTERS ( DATESBETWEEN ( 'Calendar'[Date], sd, ed ) )
        )
)

wdx223_Daniel_0-1603672467586.png

 

 

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

@Niiru1 , refer if this file, I created in the past for similar problem can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User II
Super User II

@Niiru1  firstly, you need create a date table, which have no relationship with your fact table. then try this code

DaysCount :=
SUMX (
    Table1,
    VAR sd = Table1[Date Start]
    VAR ed = Table1[Date End]
    RETURN
        CALCULATE (
            COUNT ( 'Calendar'[Date] ),
            KEEPFILTERS ( DATESBETWEEN ( 'Calendar'[Date], sd, ed ) )
        )
)

wdx223_Daniel_0-1603672467586.png

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.