Forum Discussion

kkanda's avatar
kkanda
Resolver II
5 years ago

Trying to plot previous year's data on the same chart

Hi All, 
We have a dataset comprising of outage duration for a set of dates. A simplified sample table is given below:

OutageDateMinsEquipmentCircuitWeather
8/20/2020235Cross armAIR-21NMJ
8/19/2020129CableBCD_24NON
8/17/2020201Customer EquipRiN_13MEJ
8/15/2020341CableTAN-19NON
8/14/202098PoleNAR-11MEN
..    
..    
..    

The actual data consists of 40K records dating back to the year 2013. There are some 25 types of Equipment, about 400 Circuits and 4 types of Weather. Outages are recorded as they occur - hence some dates have multiple outages and on some dates there are no outage records. The dataset is linked to a database which records continuously the outage records as they occur. 

We want to plot the sum of outage duration (in minutes) for a period, say 1/1/2019 to 12/31/2019 and compare it with the outage period 1/1/2018 to 12/31/2018. 

I created the slicers for Outage Date, Equipment, Circuit and Weather and got the plot of sum of outage mins for 2019 by selecting Outage Date slicer only. Without changing the slicer, we want to calculate the outage mins for 2018. I used the DATEADD function to get the same. 

 

When we start filtering the slicers, the Previous Year's values do not get compiled and the error message is:

 

Error Message:
MdxScript(Model) (100, 9) Calculation error in measure 'CV_SAP_OUTAGE_SUMMARY_WITH_PARTS'[TOTAL_CI_PREV_YEAR]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

 

Similar message was there when we use SAMEPERIODLASTYEAR.

Ensured that there is no time portion in the Outage date. There are gaps in date column as some dates do not have outages and the dates are NOT unique as one date can have multiple outages.

With no filters on the slicers (Select all), the values compile on monthly basis for years 2019 and 2018 and the plot is OK. The 'X-Axis' is the MONTH, derived from Outage date. 

I spent some time going through several posts on this subject in the Forum. Someone suggested creating a Date table for continuos Dates. I created a Date table with unique Dates and linked with the Outages table. 

TOTAL_OutageMins_PREV_YEAR =
CALCULATE(
SUM(Table1[Mins]),
DATEADD(CV_SAP_OUTAGE_SUMMARY_WITH_PARTS[OUTAGE_DATE],-1,YEAR)
)

I tried SUMX and also SAMEPERIODLASTYEAR but I get similar error message. 

Will the experts explain where it is going wrong when we apply the filter through the slicers? 

I am not the first person to experience; there are other posts, but the explanations are not clear.

I would appreciate if someone can let me know what is happening  and what could be the solution.

Thanking you in advance

Krishna

 

 

 

Need Help

3 Replies

    • kkanda's avatar
      kkanda
      Resolver II
      5 years ago

      Thank you Allison, I have a DimDate file with me. Do I add it to the Relationship table and relate to the Table 1?

      And the slicer for Date will be based on DimDate file?

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago
        Yes, add the DimDate table to the same Power BI report. Create a relationship between DimDate table and the data you're using based on [Outage Date] and DimDate[Date]

        Then put the DimDate[Date] in the slicer, in the DAX and in the visualization.