topic Re: Add days to date column (working days only) in DAX Commands and Tips

Add days to date column (working days only)

JC2022 — Wed, 26 Jul 2023 13:02:02 GMT

Hi,

I have tableA with these columns:

I also have a calendar table:

In the WorkingDay column the weekends and holidays are "No" and the rest is "Yes".

In a measure: I want to add the value (in days) in the Target Days column to the Date Created column. The dates with WorkingDay = "No" should not be counted as an added day.

For example: when Date Created = 26-07-2023 and Target Days = 15. 15 days later the date is 10-08-2023 but because there are 6 weekend days and 0 holidays between those 2 dates the correct date should be 16-08-2023. So basically the Target days are the working days that should be added. This should be done in a measure.

Can you help me?

Re: Add days to date column (working days only)

Anonymous — Fri, 28 Jul 2023 08:26:55 GMT

Hi JC2022 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Target Date = VAR _tab = SUMMARIZE ( FILTER ( 'calendar', 'calendar'[WorkingDay] = "Yes" && 'calendar'[Date] > 'tableA'[Date Created] ), 'calendar'[Date], 'calendar'[WorkingDay], "@culcount", IF ( 'calendar'[WorkingDay] = "No", BLANK (), CALCULATE ( COUNT ( 'calendar'[Date] ), FILTER ( ALLSELECTED ( 'calendar' ), 'calendar'[Date] <= EARLIER ( 'calendar'[Date] ) ) ) ) ) RETURN MAXX ( FILTER ( _tab, [@culcount] <= 'tableA'[Target Days] ), [Date] )

Best Regards

Re: Add days to date column (working days only)

JC2022 — Fri, 28 Jul 2023 11:08:04 GMT

Hi @v-yiruan-msft,

Thanks for your response. I have tried your formula, but I can't choose my calendar table at the arrow in the picture below. The only table I can choose there is TableA.

I am not sure but has this possibly something to do with TableA being a directquery table and Calendar being imported?

Thanks for your support!

Re: Add days to date column (working days only)

Anonymous — Wed, 02 Aug 2023 06:29:38 GMT

Hi JC2022 ,

If the table 'TableA' from a Direct Query data source, there are some limitations here.

Please try to create a measure as below to get it:

Target Date = VAR _cdate = MAX ( 'tableA'[Date Created] ) VAR _tdays = MAX ( 'tableA'[Target Days] ) VAR _tab = SUMMARIZE ( FILTER ( 'calendar', 'calendar'[WorkingDay] = "Yes" && 'calendar'[Date] > _cdate ), 'calendar'[Date], 'calendar'[WorkingDay], "@culcount", IF ( 'calendar'[WorkingDay] = "No", BLANK (), CALCULATE ( COUNT ( 'calendar'[Date] ), FILTER ( ALLSELECTED ( 'calendar' ), 'calendar'[Date] <= EARLIER ( 'calendar'[Date] ) ) ) ) ) RETURN MAXX ( FILTER ( _tab, [@culcount] <= _tdays ), [Date] )

If the above one can't help you, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Add days to date column (working days only)

JC2022 — Wed, 02 Aug 2023 09:41:45 GMT

hi Anonymous ,

A simplified part of the direct query data table is: column title with datatype in ()

Ticket (text)	Date Created (date)	Time Created (time)	DateTime Created (datetime)	Target Days (general)
RFC201106001	6-11-2020	8:47:18	6-11-2020 8:47	3
SO201106001	6-11-2020	9:18:00	6-11-2020 9:18	0,5
SO201106002	6-11-2020	9:23:13	6-11-2020 9:23	3
SO201106003	6-11-2020	9:29:15	6-11-2020 9:29	5
RFC201106002	6-11-2020	9:30:59	6-11-2020 9:30	3
SO201106005	6-11-2020	9:58:10	6-11-2020 9:58	15
RFC201106003	6-11-2020	10:01:49	6-11-2020 10:01	0,5
SO201106006	6-11-2020	10:03:33	6-11-2020 10:03	3
SO201106008	6-11-2020	10:13:00	6-11-2020 10:13	15

and the calendar table is (simplified) as you already have created in