Counting a property for 30 days CONTINUOUSLY/CONTIGUOUSLY

[AltGr9]

Hello all,

I wonder if anyone could help me with a problem related to contiguous dates.

(Also, please don't give me a "go read this other thing" type reply, as it won't help me in this case I don't think.)

I have a Fact table with Date ID, Engine ID and a Fact named Outage. The outage shows 1 on the days when an outage happened, and 0 on all other days. Each engine has a MW capacity associated with it in the Engine Dim.

When there has been a continuous outage for more than 30 days, that continues into the selected month, I want to count that engine's capacity toward the total for the measure. 

The measure only needs to return a single value for the selected Month. So let's say there were 3 engines that had an outage in the selected month, and these outages went back for 30 days or more, CONTINUOUSLY (i.e. contiguously): I want to show the total capacity for those three engines, summed up. So if they were 2K each, the measure would return 6K.

I think the problem I'm having is with the CONTIGUOUS nature of it all. 

[amitchandak]

@AltGr9 , Plot a measure like this with the date on a visual, you should date with continuous 30 days

 Rolling 30 = if(CALCULATE(distinctcount(Table[Date]),DATESINPERIOD('Table'[Date],Max(Table[Date]),-30,DAY))+0>=30,1, blank())

with date table
Rolling 30 = if(CALCULATE(distinctcount(Table[Date]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,DAY))+0>=30,1, blank())

[MFelix]

Hi @AltGr9 ,

Do you have a calendar table or you data is only composed by the fact table? 

Do you want to have the calculation made by month or is it previouly, meaning that if I'm on a certain month I need to calculate the number of days from the previous 30 + days?

[AltGr9]

Thanks MFelix.

I have all three tables: Fact table, Date Dim and Engine Dim. There is a Month slicer based on a Date Dim field.

The Fact table contains a Date ID and Engine ID, and each row in the Fact table is unique on that combination of Date ID and Engine ID.

The measure needs to sum up the Engine Capacity of those engines that match the following criteria:

• the engine has an outage in the selected month 
• the outage has to have occured on 30 contiguous days

[MFelix · Accepted solution]

Hi @AltGr9 ,

I have made the following measure:

Outage Number days = 
VAR temp_table =
    FILTER (
        ALL ( 'FACT' );
        'FACT'[Outage] = 0
            && 'FACT'[DateKey] < MAX ( DimDate[Date] )
            && 'FACT'[Engine_ID] = SELECTEDVALUE ( Engine[Engine_ID] )
    )
VAR max_Date =
    MAXX ( temp_table; 'FACT'[DateKey] )
VAR Filtered_Outage =
    FILTER (
        ALL ( 'FACT' );
        'FACT'[Outage] = 1
            && 'FACT'[DateKey] > max_Date
            && 'FACT'[DateKey] <= MAX ( DimDate[Date] )
            && 'FACT'[Engine_ID] = SELECTEDVALUE ( Engine[Engine_ID] )
    )
RETURN
    SUMX ( Filtered_Outage; 'FACT'[Outage] ) + 0

Has you can see below the calculation of the days is counting so in this case we have two months wiht values above 30:

Now what do you want to do in this months? I got lost when you say you want to calculate the capacity.

Don't mind the 0 on the total I have a solution for that adding this measure:

Total Outage Engine = SUMX(Engine;[Outage Number days])

Check PBIX attach.