Calculate an exact day between two given dates

[Syndicate_Admin]

Good morning, I would like to make a calculated measurement or column, by which I can calculate an exact calendar day.
I have a start date, an end facha, and a number to divide by.
The idea is to take the period between the start date and the end date and divide it by the "number" and get the day(s) of the calendar.

Example:
Start Date: 01/01/2022
End Date: 31/01/2022
Number to divide by: 2
It would be approx. in the middle of the month 15/01/2022
so on calculating the different dates based on the number by which to divide.

Some idea???

Thanks in advance.

[v-jingzhang · Accepted solution]

Hi @PabloSM 

I recommend to use Power Query to get the exact dates between two given dates. You can open Power Query Editor from Home > Transform data in Power BI Desktop. Then follow below steps:

1. Add a custom column to calculate the length of each period. 

Duration.Days( ([End Date] - [Start Date]) / [Period] )

2. Add the second custom column. In every cell of the column, you will have a list that has all exact dates between the given dates. 

List.Dates( Date.AddDays( [Start Date], [Period Days]), [Period] - 1, #duration([Period Days],0,0,0) )

3. For the list column, you can choose to expand it to new rows or extract values per your need. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Syndicate_Admin]

Thank you very much, it had not occurred to me to create a list within another, cube type. I have to keep learning a lot.

Best regards.

[Syndicate_Admin]

Thanks for the info, but I don't quite understand it. Here's what I've deduced:

[Plan_Accionista.Start_Date]+[Plan_Accionista.Start_Date]([Plan_Accionista.Start_Date],[Plan_Accionista.End_Date],[Plan_Accionista.Accrual_Period])/[Plan_Accionista.Accrual_Period]-1)

I don't know what is "day" and /2 is a value called Periodo_Devengo is a number. It can be several dates the result since it is not divided by 2. I don't know if I explain.

Thank you.

[amitchandak]

@Syndicate_Admin , Try a new column like

[Start Date] + Datediff([Start date],[end Date], day)/2 -1