Categorize Number of days in a month when starting date and ending day is in different month

[madlfc87]

Hello,

Based on the below screenshot I'm trying to calculate the duration of days between the start day and end day and categorise them in each month. For example if the start day is 17/9/24 and end date is 5/10/24 I'll need to put in September 14 days and in October 5 days. 

An example of an excel table to be recreated in power bi would look like this. It should also have filters for the date, location and holiday type

Thank you

[Anonymous · Accepted solution]

Thanks for the reply from smpa01 and Ritaf1983 , please allow me to provide another insight:
Hi  @madlfc87 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table=
CALENDAR(
    DATE(2023,1,1),DATE(2024,12,31))
return
ADDCOLUMNS(
    _table,"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"))

2. Create measure.

Measure =
var _mindate=MINX(FILTER(ALL('Table'),YEAR('Table'[StartDate])=MAX('Table 2'[Year])&&FORMAT('Table'[StartDate],"mmmm")=MAX('Table 2'[Month])&&'Table'[HolidayType]=MAX('Table'[HolidayType])&&'Table'[fldlocation]=MAX('Table'[fldlocation])),[StartDate])
var _maxdate=MAXX(FILTER(ALL('Table'),YEAR('Table'[EndDate])=MAX('Table 2'[Year])&&FORMAT('Table'[EndDate],"mmmm")=MAX('Table 2'[Month])&&'Table'[HolidayType]=MAX('Table'[HolidayType])&&'Table'[fldlocation]=MAX('Table'[fldlocation])),[EndDate])
RETURN
IF(
    _mindate<>BLANK() && _maxdate=BLANK(),DATEDIFF(EOMONTH(_mindate,-1),_mindate,DAY),
IF(
    _maxdate<>BLANK()&&_mindate=BLANK(),DATEDIFF(EOMONTH(_maxdate,-1),_maxdate,DAY),
IF(
    _maxdate<>BLANK()&&_mindate<>BLANK(),DATEDIFF(_mindate,_maxdate,DAY)
)))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[madlfc87]

@smpa01 

[smpa01]

@madlfc87  please provide your sample data in a way (either pasting the sample data as a table here or by uploading a power bi in one drive and ahre the link) so that I don't need to manually type the sample data to provide you the solution. Also, clearly mention the end result based on the sample data.

Also, do you need this solution in power query, dax,, datamart or other fabric artifacts (notebook)? Please specify?

[madlfc87]

https://drive.google.com/file/d/1ceuIZ-HKWSLkm8sdQZBZAXptr8k40KLx/view?usp=drive_link

I'll give you access when you need it.

The table is tblHolidayDates_all locations. It doesn't matter if it is power query or dax I just need to be able to find the number of days between the startdate and enddate and put it in the appropriate months. For instance, Start date is 15/1/23 and Enddate is 7/2/23 the result would be 17 days for January and 7 days for February. The matrix in the Dasboard should have HolidayType as rows and Year,Month as Columns. Also as filters the fldLocation, the date and HolidayType.

Thank you

[smpa01]

I can see that tblHolidayDates_all locations is coming from a sql source, you should be able to do this in server side by writing fully qualified sql queries

[madlfc87]

Im not very familiar with sql. Can you help me do it in Power BI? 

[madlfc87]

@smpa01 

[Ritaf1983]

Hi, @madlfc87 my recommendation in these kinds of scenarios is to extract the rows between the date ranges and then you can create a normal star schema with the dates table related to the date of your transactions and regular filter context will do the job.
The simple guide to extracting is here :
https://www.youtube.com/watch?v=8fogFqmPJIE

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[madlfc87]

Hi, @Ritaf1983 thank you for responding. Could you explain more? I'm having a hard time understanding what to do.

Thank you

[Ritaf1983]

Hi @madlfc87 
The idea is to split each row with a date range into rows by day. That means if you have a row with '01.02.2024 to 08.02.2024,' it will be split into 8 rows, one for each day. The implementation is based on the video I attached in the first response