Difference in Meter Reading Calculation based on dates

Hello all, Based on the below image i need some assistance either to structure the data or a calculation. If I select the Start then considering July 29, 2024 it should calculate differnce as...

Need Help

Anonymous

1 year ago

Hi harshagraj ,

I updated the formula of measure in the attached pbix file, please check if that is what you want.

Difference = 
VAR _shifttype =
    SELECTEDVALUE ( 'Table'[ShiftStartOrEnd] )
VAR _date =
    SELECTEDVALUE ( 'Table'[ReadingCaptureOn] )
VAR _shift =
    SELECTEDVALUE ( 'Table'[Shift Name] )
VAR _input =
    SELECTEDVALUE ( 'Table'[Input] )
VAR _ss1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 1"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _ss2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 2"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _ss3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 3" &&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
    )
VAR _es1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 1"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 2"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 3"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
RETURN   
    SWITCH (
        TRUE (),
        _shifttype = "Start"
            && _shift = "Shift 1", _ss1 - _ss2,
        _shifttype = "Start"
            && _shift = "Shift 2", _ss2 - _ss3,
        _shifttype = "Start"
            && _shift = "Shift 3", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 1", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 2", _es1 - _es2,
        _shifttype = "End"
            && _shift = "Shift 3", _es2 - _es3
    )

Best Regards

Difference in Meter Reading Calculation based on dates(Updated).pbix46 KB

harshagraj

Post Partisan

1 year ago

Hello Greg_Deckler thanks a lot for the reply. Actually this is a sample dataset. Technically speaking Shift1 end will be as same as Shift 2 Start and so on. I am correcting the data.

User ID	User Name	ReadingCaptureOn	Shift Name	ShiftStartOrEnd	Input	Value
174913	Jon	7/28/2024	Shift 3	Start	P1	2244567
174913	Jon	7/28/2024	Shift 3	Start	P3	2344567
174913	Jon	7/28/2024	Shift 3	Start	P2	1962234
175833	Bart	7/29/2024	Shift 1	Start	P1	2943326
175833	Bart	7/29/2024	Shift 1	Start	P3	2989733
175833	Bart	7/29/2024	Shift 1	Start	P2	3042042
175833	Bart	7/29/2024	Shift 2	End	P1	2281906
175833	Bart	7/29/2024	Shift 2	End	P3	2823592
175833	Bart	7/29/2024	Shift 2	End	P2	2969746
174913	Jon	7/29/2024	Shift 2	Start	P1	2782375
174913	Jon	7/29/2024	Shift 2	Start	P3	2982522
174913	Jon	7/29/2024	Shift 2	Start	P2	3020714
174913	Jon	7/29/2024	Shift 2	End	P1	2802801
174913	Jon	7/29/2024	Shift 2	End	P3	2605439
174913	Jon	7/29/2024	Shift 2	End	P2	2906316
174557	Wan	7/29/2024	Shift 2	Start	P1	2632718
174557	Wan	7/29/2024	Shift 2	Start	P3	2272392
174557	Wan	7/29/2024	Shift 2	Start	P2	2941521
174557	Wan	7/29/2024	Shift 3	Start	P1	2683621
174557	Wan	7/29/2024	Shift 3	Start	P3	2982543
174557	Wan	7/29/2024	Shift 3	Start	P2	2894587
174557	Wan	7/30/2024	Shift 3	End	P1	1335543
174557	Wan	7/30/2024	Shift 3	End	P3	1160300
174557	Wan	7/30/2024	Shift 3	End	P2	3326923
175833	Bart	7/30/2024	Shift 1	Start	P1	1206540
175833	Bart	7/30/2024	Shift 1	Start	P3	1050958
175833	Bart	7/30/2024	Shift 1	Start	P2	3326974
175833	Bart	7/30/2024	Shift 1	End	P1	1242837
175833	Bart	7/30/2024	Shift 1	End	P3	1081205
175833	Bart	7/30/2024	Shift 1	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	Start	P1	1242837
174913	Jon	7/30/2024	Shift 2	Start	P3	1081205
174913	Jon	7/30/2024	Shift 2	Start	P2	3326924
174557	Wan	7/30/2024	Shift 3	Start	P1	1282022
174557	Wan	7/30/2024	Shift 3	Start	P3	1115002
174557	Wan	7/30/2024	Shift 3	Start	P2	3326924
174913	Jon	7/30/2024	Shift 3	End	P1	1282022
174913	Jon	7/30/2024	Shift 3	End	P3	1115002
174913	Jon	7/30/2024	Shift 3	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	End	P1	1282022
174913	Jon	7/30/2024	Shift 2	End	P3	1115002
174913	Jon	7/30/2024	Shift 2	End	P2	3326924
175833	Bart	7/31/2024	Shift 1	Start	P1	1335543
175833	Bart	7/31/2024	Shift 1	Start	P3	1160300
175833	Bart	7/31/2024	Shift 1	Start	P2	3326924
175833	Bart	7/31/2024	Shift 1	End	P1	1367492
175833	Bart	7/31/2024	Shift 1	End	P3	1186612
175833	Bart	7/31/2024	Shift 1	End	P2	3329164
174913	Jon	7/31/2024	Shift 2	Start	P1	1367492
174913	Jon	7/31/2024	Shift 2	Start	P3	1186612
174913	Jon	7/31/2024	Shift 2	Start	P2	3329164
175422	Peter	7/31/2024	Shift 3	Start	P1	1374583
175422	Peter	7/31/2024	Shift 3	Start	P3	1194139
175422	Peter	7/31/2024	Shift 3	Start	P2	3351105
174913	Jon	7/31/2024	Shift 2	End	P1	1374583
174913	Jon	7/31/2024	Shift 2	End	P3	1194139
174913	Jon	7/31/2024	Shift 2	End	P2	3351105
175422	Peter	7/31/2024	Shift 3	End	P1	1423881
175422	Peter	7/31/2024	Shift 3	End	P3	1234736
175422	Peter	7/31/2024	Shift 3	End	P2	3351105
175833	Bart	7/29/2024	Shift 1	End	P1	2420065
175833	Bart	7/29/2024	Shift 1	End	P3	2734853
175833	Bart	7/29/2024	Shift 1	End	P2	2502956
174913	Jon	7/29/2024	Shift 1	End	P1	2298965
174913	Jon	7/29/2024	Shift 1	End	P3	2584894
174913	Jon	7/29/2024	Shift 1	End	P2	2713852
175833	Bart	7/29/2024	Shift 3	End	P1	2659880
175833	Bart	7/29/2024	Shift 3	End	P3	2595066
175833	Bart	7/29/2024	Shift 3	End	P2	2302885
174913	Jon	7/29/2024	Shift 3	End	P1	2853893
174913	Jon	7/29/2024	Shift 3	End	P3	2771344
174913	Jon	7/29/2024	Shift 3	End	P2	2429925

Anonymous

Not applicable

1 year ago

Hi harshagraj ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Difference = 
VAR _shifttype =
    SELECTEDVALUE ( 'Table'[ShiftStartOrEnd] )
VAR _date =
    SELECTEDVALUE ( 'Table'[ReadingCaptureOn] )
VAR _shift =
    SELECTEDVALUE ( 'Table'[Shift Name] )
VAR _input =
    SELECTEDVALUE ( 'Table'[Input] )
VAR _ss1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 1" )
    )
VAR _ss2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 2" )
    )
VAR _ss3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 3" )
    )
VAR _es1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 1"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 2"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
VAR _es3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Shift Name] = "Shift 3"
                && 'Table'[Input] = _input
                && 'Table'[ReadingCaptureOn] = _date + 1
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _shifttype = "Start"
            && _shift = "Shift 1", _ss1 - _ss2,
        _shifttype = "Start"
            && _shift = "Shift 2", _ss2 - _ss3,
        _shifttype = "Start"
            && _shift = "Shift 3", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 1", _ss3 - _es1,
        _shifttype = "End"
            && _shift = "Shift 2", _es1 - _es2,
        _shifttype = "End"
            && _shift = "Shift 3", _es2 - _es3
    )

Best Regards

Difference in Meter Reading Calculation based on dates.pbix46 KB

harshagraj
Post Partisan
1 year ago
Hello Anonymous thanks a lot for your inputs. In the below screenshot i have selected Start so for 07/29/2024 it should be Shift1 -Shift 2 >>2943326-5415093 = - 2,471,767 as Shift 1 and Shift 2- Shift 3 = 5415093-2683621 as Shift 2 and Shift 3 - Shift 1(07/30/2024) = 2683621-1206540.

But I am seeing different values. Could you help me?

Also for end it should calculate differnce as Shift3(July 29,2024)-Shift1(July 30,2024) at Shift1 (July 30,2024), Shift1(July 30,2024)-Shift2(July 30,2024) at Shift2 (July 30,2024) and Shift2(July 30,2024)-Shift3(July 30,2024) at Shift 3(July 30,2024)

Also if i remove Sum of value from the context the difference should still work. I need this to put on bar charts. Thanks in advance

Forum Discussion

Difference in Meter Reading Calculation based on dates

Related content

Dynamic Calculation Different Period

Different Calculations

1. How to add two different calculated columns of two different table

Calculating Differences

Power BI read aloud