Status for every Day Power Query

[Hasan29]

Hi Power BI Experts,

I need an advice how to deal with this kind of data. I would like to show how often a vehicle is in maintannce or rented etc. in a timeframe. But my Problem is that I have Start Dates and End Dates. That means for me I can’t simply upload the data and connect it with my Date Table.

ID	Status	Start Date	End Date
1	maintenance	01.01.2022	23.03.2022
2	maintenance	06.04.2022	09.04.2022
4	not rented	01.04.2021	03.08.2022
4	rented	04.08.2022	31.12.2022
3	maintenance	08.08.2022	08.09.2022
3	not rented	09.09.2022	31.09.2022
1	rented	24.04.2022	30.08.2022

1. Is there a way to Change the Form of this Data in Power Query? I would then get the Status of each Day and connect the new table with my Date Table. Or combine a Date Table.
2. Or should I use VBA to change the Data and then upload to Power BI.

Thank You!

[Hasan29]

Hi jbwtb,

Thank you for your answer. I would like to show the Number of days. And I would need a % of the Number of Days. For example August Number of Days / 31 Days. 

The best or easiest way. I dont know what is easier dax or query. 

Regards,

[Hasan29]

Short Update: The Data looks like this: 

[jbwtp]

Hi @Hasan29,

"how often" is this a number of times (regardless of how long) or a number of days (may be as a % to the total days in the timeframe)?

Also do you expect to do it in DAX or using Power Query?

Cheers,

John

[Hasan29]

Hi jbwtb,

Thank you for your answer. I would like to show the Number of days. And I would need a % of the Number of Days. For example August Number of Days / 31 Days. 

The best or easiest way. I dont know what is easier dax or query. 

Regards,

[jbwtp]

Hi @Hasan29 

Assuming that we have two tables like this:

Dates

etc.

Items

This is the measure to calcualte % on minatenance, you can create other using this as a template:

OnMaintenance = sumx(filter(Items, Items[Status] = "maintenance"), max(0,1+DATEDIFF(max(Items[Start Date], MINX(Dates, [Date])), min(Items[End Date], MAXX(Dates, [Date])), DAY))) / (DATEDIFF(STARTOFMONTH(Dates[Date]), ENDOFMONTH(Dates[Date]), DAY)+1)

Note that the tables are not linked to each other (i.e. no arrows between them on the relationships view).

This is the output view:

P.S. Sorry need to clarify: Month is the date foeld from dates (just grouped):

Kind regards,

John

[Hasan29]

Hi,

thank you for your answer. The formular does not work for me.

After "maintenance") I cant continue the Code with ","  (syntax is incorrect). I have a Date table just like that. 

Thank you.

Regards,

Hasan

[jbwtp]

Hi Hasan,

Sorry, I can't see anything wrong with the formula.

Do you mind sending me a screenshot of the formula that throws the error?

Thanks,

John