Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Spread duration across month

I have a list of Asset Outages, and a cross-joined table that generates a record per month per asset. I need to summarize the total outage downtime (hrs) per month per asset into this second table, where some outages span months.

Outage Table e.g.:

Outage ID | Asset ID | Outage Start | Outage End | CalcDuration(Hrs)

001 | XYZ1 | 3-Jan-2019 10:00am | 6-Jan-2019 04:00pm | 78

002 | ABC2 | 10-Jan-2019 09:00am | 10-Jan-2019 06:00pm | 9

003 | XYZ1 | 26-Jan-2019 08:00am | 6-Feb-2019 08:00pm | 276

004 | ABC2 | 10-Feb-2019 10:00pm | 16-Feb-2019 02:00pm | 136

005 | XYZ1 | 12-Feb-2019 05:00am | 13-Feb-2019 04:00pm | 35

That I need to get the calculated duration summarized into my monthly summary table. Currently created with:

Monthly Summary Table =CROSSJOIN('Month Table','Asset Table')

Asset ID | MonthYear | MonthlyDowntime Duration(Hrs)

ABC2 | Jan-2019 | 9

XYZ1 | Jan-2019 | 214

ABC2 | Feb-2019 | 136

XYZ1 | Feb-2019 | 175

I already have the tables created it's just the italics column - summarizing the outage duration into the relevant month rows for each asset.

Other tables mentioned above structured as follows:

Caledar Table - =CALENDAR(DATE(2019,01,01),MAX('OutageTable'[Outage End].[DATE])

Date | CalculatedMonthYear

DD-MMM-YY | =FORMAT('Calendar Table'[Date],"MMM-YYYY"

Month Table - =SUMMARIZE('Calendar Table','Calendar Table'[CalculatedMonthYear])

CalculatedMonthYear |

MMM-YYYY

Asset Table:

Asset ID |

ABC2 |

XYZ1 |

Need Help

Anonymous
6 years ago
Thanks, but the measure version would just not be sustainable for our use - as there are multipel further steps to be perfomred once we calculate this monthly apportionment of the duration. This would entail multiple measures within measures - impacting the performance of our dashboard.
Data only being refreshed on import / refresh is fine for what we need.

We actually solved in a different way - using the "Split Columns" during the import query to split each multi-month record into separate records for each month it spans.

The logic basically creates various "Start - End Point" text fields, to cover the various middle months. Then concatenates them into a single field, and then uses this field to apply the "Split Colums" function.

Below is the query-editor view of the new import query - which covers outages that span up to 3 months (we had to create further steps for other imports to cover up to 13 month spans):

LVB_Outages_Table = Source{[Item="LVB_Outages",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(LVB_Outages_Table,{{"DownTime", type number}, {"FaultCategory", type text}, {"FaultItem", type text}, {"Controllable", type text}, {"DoNotAutoClose", type text}, {"InitialCategory", type text}, {"OpenAuto", type text}, {"CloseAuto", type text}, {"Clean Bank Code", type text}, {"ATMStatus", type text}, {"CallStatus", type text}, {"StatesName", type text}, {"StartDate", type datetime}, {"Close_Date", type datetime}, {"Location", type text}, {"SerialNo", type text}, {"Site", type text}, {"Region", type text}, {"PMNonPM", type text}, {"CallType", type text}, {"ATMId", type text}, {"SrNo", type text}, {"IncidentId", type text}}),
#"Added EndOfMonth - StartDate" = Table.AddColumn(#"Changed Type", "EndOfMonth - StartDate", each Date.EndOfMonth([StartDate])),
#"Added StartOfMonth - CloseDate" = Table.AddColumn(#"Added EndOfMonth - StartDate", "StartOfMonth - CloseDate", each Date.StartOfMonth([Close_Date])),
#"Merged Column: StartDate to CloseDate" = Table.AddColumn(#"Added StartOfMonth - CloseDate", "StartDate to CloseDate", each Text.Combine({Text.From([StartDate], "en-US"), Text.From([Close_Date], "en-US")}, ","), type text),
#"Merged Column: StartDate to EOM" = Table.AddColumn(#"Merged Column: StartDate to CloseDate", "StartDate to EOM", each Text.Combine({Text.From([StartDate], "en-US"), Text.From([#"EndOfMonth - StartDate"], "en-US")}, ","), type text),
#"Merged Column: SOM to CloseDate" = Table.AddColumn(#"Merged Column: StartDate to EOM", "SOM to CloseDate", each Text.Combine({Text.From([#"StartOfMonth - CloseDate"], "en-US"), Text.From([Close_Date], "en-US")}, ","), type text),
#"Added MiddleMonth1 - SOM" = Table.AddColumn(#"Merged Column: SOM to CloseDate", "MiddleMonth1 - SOM", each Date.StartOfMonth(Date.AddMonths([StartDate], 1))),
#"Added MiddleMonth1 - EOM" = Table.AddColumn(#"Added MiddleMonth1 - SOM", "MiddleMonth1 - EOM", each Date.EndOfMonth(Date.AddMonths([StartDate], 1))),
#"Merged Column: MiddleMonth1 EndPoints" = Table.AddColumn(#"Added MiddleMonth1 - EOM", "MiddleMonth1 EndPoints", each Text.Combine({Text.From([#"MiddleMonth1 - SOM"], "en-US"), Text.From([#"MiddleMonth1 - EOM"], "en-US")}, ","), type text),
#"Added MiddleMonth2 - SOM" = Table.AddColumn(#"Merged Column: MiddleMonth1 EndPoints", "MiddleMonth2 - SOM", each Date.StartOfMonth(Date.AddMonths([StartDate], 2))),
#"Added MiddleMonth2 - EOM" = Table.AddColumn(#"Added MiddleMonth2 - SOM", "MiddleMonth2 - EOM", each Date.EndOfMonth(Date.AddMonths([StartDate], 2))),
#"Merged Column: MiddleMonth2 EndPoints" = Table.AddColumn(#"Added MiddleMonth2 - EOM", "MiddleMonth2 EndPoints", each Text.Combine({Text.From([#"MiddleMonth2 - SOM"], "en-US"), Text.From([#"MiddleMonth2 - EOM"], "en-US")}, ","), type text),
#"Merged Column: OneMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: MiddleMonth2 EndPoints", "OneMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [SOM to CloseDate]}, ";"), type text),
#"Merged Column: TwoMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: OneMonthSpan EndPoints", "TwoMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [MiddleMonth1 EndPoints], [SOM to CloseDate]}, ";"), type text),
#"Merged Column: ThreeMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: TwoMonthSpan EndPoints", "ThreeMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [MiddleMonth1 EndPoints], [MiddleMonth2 EndPoints], [SOM to CloseDate]}, ";"), type text),
#"Conditional Column: EndPoints List" = Table.AddColumn(#"Merged Column: ThreeMonthSpan EndPoints", "EndPoints List", each if [MonthsBetween] = 0 then [StartDate to CloseDate] else if [MonthsBetween] = 1 then [OneMonthSpan EndPoints] else if [MonthsBetween] = 2 then [TwoMonthSpan EndPoints] else if [MonthsBetween] = 3 then [ThreeMonthSpan EndPoints] else "CHECK"),
#"Removed Columns" = Table.RemoveColumns(#"Conditional Column: EndPoints List",{"EndOfMonth - StartDate", "StartOfMonth - CloseDate", "StartDate to CloseDate", "StartDate to EOM", "SOM to CloseDate", "MiddleMonth1 - SOM", "MiddleMonth1 - EOM", "MiddleMonth1 EndPoints", "MiddleMonth2 - SOM", "MiddleMonth2 - EOM", "MiddleMonth2 EndPoints", "OneMonthSpan EndPoints", "TwoMonthSpan EndPoints", "ThreeMonthSpan EndPoints"}),
#"Split Column to Records by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"EndPoints List", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EndPoints List"),
#"Changed EndPoints List Type to TEXT" = Table.TransformColumnTypes(#"Split Column to Records by Delimiter",{{"EndPoints List", type text}}),
#"Split Column to Columns by Delimiter" = Table.SplitColumn(#"Changed EndPoints List Type to TEXT", "EndPoints List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"EndPoint.1", "EndPoint.2"}),
#"Changed EndPoints Type to DATE" = Table.TransformColumnTypes(#"Split Column to Columns by Delimiter",{{"EndPoint.1", type datetime}, {"EndPoint.2", type datetime}}),
#"Renamed EndPoints Columns" = Table.RenameColumns(#"Changed EndPoints Type to DATE",{{"EndPoint.1", "StartDate for Calc"}, {"EndPoint.2", "CloseDate for Calc"}})
in
#"Renamed EndPoints Columns"

7 Replies

amitchandak
Super User
6 years ago
Anonymous , this file has very similar logic

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0

check how table 2 is created
- Anonymous
  Not applicable
  6 years ago
  Thanks amitchandak, but I don't think that will work.
  
  The logic you use to create table 2 effectively creates a daily record for each day of each contract - I am trying to calculate duration down to the second for millions of outages, some lasting many days.
  1 record per outage per second of the outage is going to grow pretty quick!!
v-xicai
Community Support
6 years ago
Hi Anonymous ,

You may create calendar table first, then create measure like DAX below.

Table: Calendar=CALENDARAUTO() Measure: MonthlyDowntime Duration(Hrs)= var _Date=SELECTEDVALUE('Calendar'[Date]) return CALCULATE(SUM(Outage[CalcDuration(Hrs)] ), FILTER(Outage, Outage[Outage Start]<=_Date &&_Date<=Outage[Outage End] &&Outage[Asset ID]=MAX(Outage[Asset ID])))

Then you may format the data type of 'Calendar'[Date] as "MMM-YYYY" , put Outage[Asset ID], 'Calendar'[Date] and Outage[MonthlyDowntime Duration(Hrs)] into Table visual, instead of creating a new calculated table.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  6 years ago
  Thanks Amy, unfortunately I need to make this into a column as I have further calculations I need to do once I identify total downtime per month, per asset.
  
  I also get an error when I use that measure - "Operator of exprerssion '()' is not supported in this context.
  I tried using my original calendar table in place of the one you suggested and still get this error.
  - v-xicai
    Community Support
    6 years ago
    Hi Anonymous ,
    
    For the error message, I have correct the formula above, which there is an extra bracket "(" .
    
    The reason to use measure instead of column is that you couldn't have a calculated column interacted with a slicer or interact with other visuals in time , since columns are only calculated when data is loaded/refreshed.
    
    Best Regards,
    
    Amy
- v-xicai
  Community Support
  6 years ago
  Hi Anonymous ,
  
  Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
  
  Best regards
  
  Amy
  - Anonymous
    Not applicable
    6 years ago
    Thanks, but the measure version would just not be sustainable for our use - as there are multipel further steps to be perfomred once we calculate this monthly apportionment of the duration. This would entail multiple measures within measures - impacting the performance of our dashboard.
    Data only being refreshed on import / refresh is fine for what we need.
    
    We actually solved in a different way - using the "Split Columns" during the import query to split each multi-month record into separate records for each month it spans.
    
    The logic basically creates various "Start - End Point" text fields, to cover the various middle months. Then concatenates them into a single field, and then uses this field to apply the "Split Colums" function.
    
    Below is the query-editor view of the new import query - which covers outages that span up to 3 months (we had to create further steps for other imports to cover up to 13 month spans):
    
    LVB_Outages_Table = Source{[Item="LVB_Outages",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(LVB_Outages_Table,{{"DownTime", type number}, {"FaultCategory", type text}, {"FaultItem", type text}, {"Controllable", type text}, {"DoNotAutoClose", type text}, {"InitialCategory", type text}, {"OpenAuto", type text}, {"CloseAuto", type text}, {"Clean Bank Code", type text}, {"ATMStatus", type text}, {"CallStatus", type text}, {"StatesName", type text}, {"StartDate", type datetime}, {"Close_Date", type datetime}, {"Location", type text}, {"SerialNo", type text}, {"Site", type text}, {"Region", type text}, {"PMNonPM", type text}, {"CallType", type text}, {"ATMId", type text}, {"SrNo", type text}, {"IncidentId", type text}}),
    #"Added EndOfMonth - StartDate" = Table.AddColumn(#"Changed Type", "EndOfMonth - StartDate", each Date.EndOfMonth([StartDate])),
    #"Added StartOfMonth - CloseDate" = Table.AddColumn(#"Added EndOfMonth - StartDate", "StartOfMonth - CloseDate", each Date.StartOfMonth([Close_Date])),
    #"Merged Column: StartDate to CloseDate" = Table.AddColumn(#"Added StartOfMonth - CloseDate", "StartDate to CloseDate", each Text.Combine({Text.From([StartDate], "en-US"), Text.From([Close_Date], "en-US")}, ","), type text),
    #"Merged Column: StartDate to EOM" = Table.AddColumn(#"Merged Column: StartDate to CloseDate", "StartDate to EOM", each Text.Combine({Text.From([StartDate], "en-US"), Text.From([#"EndOfMonth - StartDate"], "en-US")}, ","), type text),
    #"Merged Column: SOM to CloseDate" = Table.AddColumn(#"Merged Column: StartDate to EOM", "SOM to CloseDate", each Text.Combine({Text.From([#"StartOfMonth - CloseDate"], "en-US"), Text.From([Close_Date], "en-US")}, ","), type text),
    #"Added MiddleMonth1 - SOM" = Table.AddColumn(#"Merged Column: SOM to CloseDate", "MiddleMonth1 - SOM", each Date.StartOfMonth(Date.AddMonths([StartDate], 1))),
    #"Added MiddleMonth1 - EOM" = Table.AddColumn(#"Added MiddleMonth1 - SOM", "MiddleMonth1 - EOM", each Date.EndOfMonth(Date.AddMonths([StartDate], 1))),
    #"Merged Column: MiddleMonth1 EndPoints" = Table.AddColumn(#"Added MiddleMonth1 - EOM", "MiddleMonth1 EndPoints", each Text.Combine({Text.From([#"MiddleMonth1 - SOM"], "en-US"), Text.From([#"MiddleMonth1 - EOM"], "en-US")}, ","), type text),
    #"Added MiddleMonth2 - SOM" = Table.AddColumn(#"Merged Column: MiddleMonth1 EndPoints", "MiddleMonth2 - SOM", each Date.StartOfMonth(Date.AddMonths([StartDate], 2))),
    #"Added MiddleMonth2 - EOM" = Table.AddColumn(#"Added MiddleMonth2 - SOM", "MiddleMonth2 - EOM", each Date.EndOfMonth(Date.AddMonths([StartDate], 2))),
    #"Merged Column: MiddleMonth2 EndPoints" = Table.AddColumn(#"Added MiddleMonth2 - EOM", "MiddleMonth2 EndPoints", each Text.Combine({Text.From([#"MiddleMonth2 - SOM"], "en-US"), Text.From([#"MiddleMonth2 - EOM"], "en-US")}, ","), type text),
    #"Merged Column: OneMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: MiddleMonth2 EndPoints", "OneMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [SOM to CloseDate]}, ";"), type text),
    #"Merged Column: TwoMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: OneMonthSpan EndPoints", "TwoMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [MiddleMonth1 EndPoints], [SOM to CloseDate]}, ";"), type text),
    #"Merged Column: ThreeMonthSpan EndPoints" = Table.AddColumn(#"Merged Column: TwoMonthSpan EndPoints", "ThreeMonthSpan EndPoints", each Text.Combine({[StartDate to EOM], [MiddleMonth1 EndPoints], [MiddleMonth2 EndPoints], [SOM to CloseDate]}, ";"), type text),
    #"Conditional Column: EndPoints List" = Table.AddColumn(#"Merged Column: ThreeMonthSpan EndPoints", "EndPoints List", each if [MonthsBetween] = 0 then [StartDate to CloseDate] else if [MonthsBetween] = 1 then [OneMonthSpan EndPoints] else if [MonthsBetween] = 2 then [TwoMonthSpan EndPoints] else if [MonthsBetween] = 3 then [ThreeMonthSpan EndPoints] else "CHECK"),
    #"Removed Columns" = Table.RemoveColumns(#"Conditional Column: EndPoints List",{"EndOfMonth - StartDate", "StartOfMonth - CloseDate", "StartDate to CloseDate", "StartDate to EOM", "SOM to CloseDate", "MiddleMonth1 - SOM", "MiddleMonth1 - EOM", "MiddleMonth1 EndPoints", "MiddleMonth2 - SOM", "MiddleMonth2 - EOM", "MiddleMonth2 EndPoints", "OneMonthSpan EndPoints", "TwoMonthSpan EndPoints", "ThreeMonthSpan EndPoints"}),
    #"Split Column to Records by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"EndPoints List", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EndPoints List"),
    #"Changed EndPoints List Type to TEXT" = Table.TransformColumnTypes(#"Split Column to Records by Delimiter",{{"EndPoints List", type text}}),
    #"Split Column to Columns by Delimiter" = Table.SplitColumn(#"Changed EndPoints List Type to TEXT", "EndPoints List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"EndPoint.1", "EndPoint.2"}),
    #"Changed EndPoints Type to DATE" = Table.TransformColumnTypes(#"Split Column to Columns by Delimiter",{{"EndPoint.1", type datetime}, {"EndPoint.2", type datetime}}),
    #"Renamed EndPoints Columns" = Table.RenameColumns(#"Changed EndPoints Type to DATE",{{"EndPoint.1", "StartDate for Calc"}, {"EndPoint.2", "CloseDate for Calc"}})
    in
    #"Renamed EndPoints Columns"

Forum Discussion

Spread duration across month

7 Replies

Related content

Calculate spread

Spread project value evenly accross project duration

notebook duration time issue

duration format

Reset Performance analyzer- For consistent duration