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

How to find days between two dates for each month of a year?

Hello, 

 

I have data that currently exist in this format:

ProjectStart Date End Date
 115-Jan-202220-Feb-2022
210-Jan-202225-Mar-2022
315-Feb-202220-Jun-2022

 

My user is interested in looking at Number of Days between Start and End Date by Month for each Year. How can we achieve the below  output using formulas or any other approch:

S.NoStart Date End DateJan-22Feb-22Mar-22Apr-22May-22Jun-22
115-Jan-202220-Feb-20221620    
210-Jan-202225-Mar-2022212925   
315-Feb-202220-Jun-2022 1431303120

 

Any ideas on how to do this on a Sharepoint List, Power App or eventually Power BI?

 

Many thanks in advance !

C

1 ACCEPTED SOLUTION

Accepted Solutions
TheRobRush
Super User
Super User

ClearCollect(
    RangeBreakdownTotals,
    AddColumns(
GroupBy(
Ungroup(
           ForAll(

DateRangeRequest,


With({startDate:ThisRecord.Start,
      sequenceDays: Sequence(DateDiff(ThisRecord.Start,
                                      ThisRecord.End) + 1)
      },
      AddColumns(sequenceDays,
                 "Date",
                 DateAdd(startDate, Value - 1, Days),
                 "Month",Month(DateAdd(startDate, Value - 1, Days)),
                 "MonthYear",Concatenate(Month(DateAdd(startDate, Value - 1, Days)),Year(DateAdd(startDate, Value - 1, Days)))
                 ///"Month",Concatenate(Month(DateAdd(startDate, Value - 1, Days)),Year(DateAdd(startDate, Value - 1, Days)))
       )


)),"Value"


///),"Month","MonthDates"
),"MonthYear","MonthYearDates"
),"DaysThisMonth",CountRows(ThisRecord.MonthYearDates)
))

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

View solution in original post

6 REPLIES 6
EddieE
Solution Sage
Solution Sage

@Capuchino 

You can do this in Power BI using the solution here. You end up with this table, which I added a '+1' to the formula to include EndDate in the count:

EddieE_0-1648594372217.png

Adjusted Measure:

CountInDateRange = 
VAR StartDate = VALUE( SELECTEDVALUE( ProjectData[Start Date ] ) )
VAR EndDate = VALUE( SELECTEDVALUE( ProjectData[End Date] ) )
VAR MinDateInContext = VALUE( MIN( Dates[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) )

RETURN
 IF( AND( StartDate < MinDateInContext, EndDate > MinDateInContext ),
        MIN( EndDate, MaxDateInContext ) - MinDateInContext + 1,
            IF( AND( AND( StartDate > MinDateInContext, StartDate < MaxDateInContext ), EndDate > MinDateInContext ),
                 MIN( EndDate, MaxDateInContext + 1 ) - StartDate ,
                    Blank() ) ) 
 

To set this up you need the data similar to that you supplied above in a table called ProjectData and a date table called Dates with a column named [Date] with all dates in the range that you wish to count against. If you are not familiar with setting up a date table there are plenty of examples via Google, with code you can copy and paste. You don't need to setup a relationship between the Date table and the ProjectData table.

 

In PowerApps, you could solve this using the Sequence() function, however, the dynamic nature of your Year/Month columns would prove difficult to achieve - not impossible though

TheRobRush
Super User
Super User

Ok so the following code, placed anywhere it can be triggered to run (In my case I placed it in the OnSelect of a button), will cycle through a set of start and end dates (In my example it is going through a collection that currently has 3 sets of start and end dates, the third of which starts in July and ends in September just to see if 3 months would break it)

 

This formula returns a collection with the following columns

 

Month: This is the number of the month, can change it in formula to be text value of month if needed, or just anywhere you display it, convert on the control it's being displayed on

DaysThisMonth: Total count of days requested off in that month

MonthDates: Nested table that includes every day selected for the request in that specific month.

 

 

ClearCollect(
    RangeBreakdownTotals,
    AddColumns(
GroupBy(
Ungroup(
           ForAll(

DateRangeRequest,


With({startDate:ThisRecord.Start,
      sequenceDays: Sequence(DateDiff(ThisRecord.Start,
                                      ThisRecord.End) + 1)
      },
      AddColumns(sequenceDays,
                 "Date",
                 DateAdd(startDate, Value - 1, Days),
                 "Month",Month(DateAdd(startDate, Value - 1, Days))
       )


)),"Value"


),"Month","MonthDates"
),"DaysThisMonth",CountRows(ThisRecord.MonthDates)
))

 

 

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Hello The RobRush, 

 

Amazing formula !

The formula works well thank you, however I will need to have the Years as well  because for the moment if I have some days in march 2022 and some days in march 2023, it sum up all March days. 

 

The idea is to be able to multiply the number of days with a specific number of hours so I got the workload in hours per months of the year per project.  

Could you please help me to add the years?

Best regards, 

Capucine  

TheRobRush
Super User
Super User

ClearCollect(
    RangeBreakdownTotals,
    AddColumns(
GroupBy(
Ungroup(
           ForAll(

DateRangeRequest,


With({startDate:ThisRecord.Start,
      sequenceDays: Sequence(DateDiff(ThisRecord.Start,
                                      ThisRecord.End) + 1)
      },
      AddColumns(sequenceDays,
                 "Date",
                 DateAdd(startDate, Value - 1, Days),
                 "Month",Month(DateAdd(startDate, Value - 1, Days)),
                 "MonthYear",Concatenate(Month(DateAdd(startDate, Value - 1, Days)),Year(DateAdd(startDate, Value - 1, Days)))
                 ///"Month",Concatenate(Month(DateAdd(startDate, Value - 1, Days)),Year(DateAdd(startDate, Value - 1, Days)))
       )


)),"Value"


///),"Month","MonthDates"
),"MonthYear","MonthYearDates"
),"DaysThisMonth",CountRows(ThisRecord.MonthYearDates)
))

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
Capuchino
Helper I
Helper I

@TheRobRush  Many many thanks for your help!

Hi,

can you give the crux of the solution please,

it's totally confusing to read🙂

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,544)