Forum Discussion

Mangnuel's avatar
Mangnuel
Helper I
2 years ago
Solved

Dates inbetween?

Hello PowerBi World

 

My goal is highlighting within a figure the availability of a system.
What do I have, the beginning and ending date of an outage. Two inputs.
That’s the information PowerBI currently has.

What do I want, all the dates between the beginning and ending outage.
So one can generate a figure with daily resolution showing the availability of a system.
Easy example.

A figure including every single day is needed.
Beginning date of outage on Mar 9th, Ending Date March 19th . Those two dates are known by the system. How can I create such a table now.

 

at the beginning of the month the number of outages is 0, with the beginning date the figure turns to 1 (outage) until the problem was resolved. Then the line goes back to 0.

Thanks in advance

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mangnuel ,

     

    Based on your description, I created these data.

    1. create the calculation table.

    New Table =
VAR _start_date = EOMONTH(MIN('Table'[start]),-1) + 1
VAR _end_date = EOMONTH(MAX('Table'[end]),0)
RETURN
FILTER(
CALENDAR(_start_date,_end_date), [Date] >= _start_date, [Date] >= _start_date
[Date] >= _start_date && [Date] <= _end_date)

     

    2. Create calculated columns in the calculation table.

    Column =
IF(
COUNTROWS(
FILTER(
'Table'.
'New Table'[Date] >= 'Table'[start] && 'New Table'[Date] <= 'Table'[end]
)
) > 0, 1, 0
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mangnuel ,

     

    Based on your description, I created these data.

    1. create the calculation table.

    New Table =
VAR _start_date = EOMONTH(MIN('Table'[start]),-1) + 1
VAR _end_date = EOMONTH(MAX('Table'[end]),0)
RETURN
FILTER(
CALENDAR(_start_date,_end_date), [Date] >= _start_date, [Date] >= _start_date
[Date] >= _start_date && [Date] <= _end_date)

     

    2. Create calculated columns in the calculation table.

    Column =
IF(
COUNTROWS(
FILTER(
'Table'.
'New Table'[Date] >= 'Table'[start] && 'New Table'[Date] <= 'Table'[end]
)
) > 0, 1, 0
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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