v8.0

Use Case 8 - Difference analysis for any time period

Case 8: Difference analysis for any time period

Requirement

In smart electricity consumption, the electricity consumption record of each meter is collected by frequency.

For example, the collection frequency is every 10 minutes (possibly in seconds, minutes, and hours). During the analysis, it is necessary to calculate the electricity consumption in this time range (end-time electricity consumption minus start-time electricity consumption), which will be displayed according to different dimensions such as hour, day, month, and year.

An example of the original statistics is as follows, which is counted every ten minutes.

The actual electricity consumption for each time period needs to be subtracted from the current row of data from the next row.

Steps for creating a dashboard

Drag a Data table.
Add a data column in the table 'Electricity'.
Add the following expression as below. Name the column as Actual Electricity Consumption.

LEADW(
    null,
    'Electricity'[Electricity Value],
    PARTITIONBY(
        'Electricity'[Device ID]
        ),
    ORDERBY(
        'Electricity'[Collection DateTime]
        )
    )
    -
    'Electricity'[Electricity Value]

Binding the Actual Electricity Consumption to Columns, it can be seen that each row gets the value of the next row minus the current row.

In this way, the electricity consumption of each 10 minutes is obtained.

The below example is a subtraction analysis for current Year.

Drag a chart and drag a CollectionDateTime(Day) or CollectionDateTime(Month) or CollectionDateTime(Hour) to the Axis.
Add 3 measures in the table.
The first measure is CurrentDayElectricity and the expression is as below:

CALCULATE(
  sumx(        
            ADDCOLUMNS(        
                SELECTATTRIBUTES(      
                    'Electricity'[Electricity Value]        
                ),         
                "Electricity_Collection DateTime_Hour",         
                HOUR(          
                    'Electricity'[Collection DateTime]          
                ),         
                "subtractValue",           
                CALCULATE(         
                    LEADW(         
                            ADDCOLUMNS(        
                                SELECTATTRIBUTES(          
                                    'Electricity'[Electricity Value],           
                                    'Electricity'[Collection DateTime]          
                                ),         
                                "Electricity_Collection DateTime_Hour",         
                                HOUR(          
                                    'Electricity'[Collection DateTime]          
                                )          
                            ),         
                        'Electricity'[Electricity Value],           
                        PARTITIONBY(   
                            [Electricity_Collection DateTime_Hour]          
                        ),         
                        ORDERBY(           
                            'Electricity'[Collection DateTime]          
                        )          
                    )          
                    -          
                    'Electricity'[Electricity Value]        
                )          
            ),         
            [subtractValue]        
        ),
    YEAR('Electricity'[Collection DateTime])=YEAR(TODAY()) && MONTH('Electricity'[Collection DateTime])=MONTH(TODAY()) &&  DAY('Electricity'[Collection DateTime])=DAY(TODAY())
)

The second measure is CurrentMonthElectricity and the expression is as below:

CALCULATE(
  sumx(        
            ADDCOLUMNS(        
                SELECTATTRIBUTES(      
                    'Electricity'[Electricity Value]        
                ),         
                "Electricity_Collection DateTime_Day",          
                DAY(           
                    'Electricity'[Collection DateTime]          
                ),         
                "subtractValue",           
                CALCULATE(         
                    LEADW(         
                            ADDCOLUMNS(        
                                SELECTATTRIBUTES(          
                                    'Electricity'[Electricity Value],           
                                    'Electricity'[Collection DateTime]          
                                ),         
                                "Electricity_Collection DateTime_Day",          
                                DAY(           
                                    'Electricity'[Collection DateTime]          
                                )          
                            ),         
                        'Electricity'[Electricity Value],           
                        PARTITIONBY(   
                            [Electricity_Collection DateTime_Day]           
                        ),         
                        ORDERBY(           
                            'Electricity'[Collection DateTime]          
                        )          
                    )          
                    -          
                    'Electricity'[Electricity Value]        
                )          
            ),         
            [subtractValue]        
        ),
    YEAR('Electricity'[Collection DateTime])=YEAR(TODAY()) && MONTH('Electricity'[Collection DateTime])=MONTH(TODAY())
)

The third measure is CurrentYearElectricity and the expression is as below:

CALCULATE(
  sumx(        
            ADDCOLUMNS(        
                SELECTATTRIBUTES(      
                    'Electricity'[Electricity Value]        
                ),         
                "Electricity_Collection DateTime_Month",        
                MONTH(         
                    'Electricity'[Collection DateTime]          
                ),         
                "subtractValue",           
                CALCULATE(         
                    LEADW(         
                            ADDCOLUMNS(        
                                SELECTATTRIBUTES(          
                                    'Electricity'[Electricity Value],           
                                    'Electricity'[Collection DateTime]          
                                ),         
                                "Electricity_Collection DateTime_Month",        
                                MONTH(         
                                    'Electricity'[Collection DateTime]          
                                )          
                            ),         
                        'Electricity'[Electricity Value],           
                        PARTITIONBY(   
                            [Electricity_Collection DateTime_Month]         
                        ),         
                        ORDERBY(           
                            'Electricity'[Collection DateTime]          
                        )          
                    )          
                    -          
                    'Electricity'[Electricity Value]        
                )          
            ),         
            [subtractValue]        
        ),
    YEAR('Electricity'[Collection DateTime])=YEAR(TODAY())
)

Drag the measure CurrentYearElectricity to Values.