Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

Reply
harshagraj
Post Partisan
Post Partisan

Difference in Meter Reading Calculation based on dates

‎10-09-2024 08:15 AM

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 Shift1-Shift2 at Shift1, Shift2-Shift3 at Shift2 and Shift3-Shift1(July 30,2024) at Shift 3 (July 29,2024) 
If I select the End then considering July 29, 2024 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)

harshagraj_0-1728486503250.png
Please guide me how to solve this.Attaching sample data.

User IDUser NameReadingCaptureOnShift NameShiftStartOrEndInputValue
174913Jon7/28/2024Shift 3StartP12244567
174913Jon7/28/2024Shift 3StartP32344567
174913Jon7/28/2024Shift 3StartP21962234
175833Bart7/29/2024Shift 1StartP12943326
175833Bart7/29/2024Shift 1StartP32989733
175833Bart7/29/2024Shift 1StartP23042042
175833Bart7/29/2024Shift 2EndP12281906
175833Bart7/29/2024Shift 2EndP32823592
175833Bart7/29/2024Shift 2EndP22969746
174913Jon7/29/2024Shift 2StartP12782375
174913Jon7/29/2024Shift 2StartP32982522
174913Jon7/29/2024Shift 2StartP23020714
174913Jon7/29/2024Shift 2EndP12802801
174913Jon7/29/2024Shift 2EndP32605439
174913Jon7/29/2024Shift 2EndP22906316
174557Wan7/29/2024Shift 2StartP12632718
174557Wan7/29/2024Shift 2StartP32272392
174557Wan7/29/2024Shift 2StartP22941521
174557Wan7/29/2024Shift 3StartP12683621
174557Wan7/29/2024Shift 3StartP32982543
174557Wan7/29/2024Shift 3StartP22894587
174557Wan7/30/2024Shift 3EndP11335543
174557Wan7/30/2024Shift 3EndP31160300
174557Wan7/30/2024Shift 3EndP23326923
175833Bart7/30/2024Shift 1StartP11206540
175833Bart7/30/2024Shift 1StartP31050958
175833Bart7/30/2024Shift 1StartP23326974
175833Bart7/30/2024Shift 1EndP11242837
175833Bart7/30/2024Shift 1EndP31081205
175833Bart7/30/2024Shift 1EndP23326924
174913Jon7/30/2024Shift 2StartP11242837
174913Jon7/30/2024Shift 2StartP31081205
174913Jon7/30/2024Shift 2StartP23326924
174557Wan7/30/2024Shift 3StartP11282022
174557Wan7/30/2024Shift 3StartP31115002
174557Wan7/30/2024Shift 3StartP23326924
174913Jon7/30/2024Shift 3EndP11282022
174913Jon7/30/2024Shift 3EndP31115002
174913Jon7/30/2024Shift 3EndP23326924
174913Jon7/30/2024Shift 2EndP11282022
174913Jon7/30/2024Shift 2EndP31115002
174913Jon7/30/2024Shift 2EndP23326924
175833Bart7/31/2024Shift 1StartP11335543
175833Bart7/31/2024Shift 1StartP31160300
175833Bart7/31/2024Shift 1StartP23326924
175833Bart7/31/2024Shift 1EndP11367492
175833Bart7/31/2024Shift 1EndP31186612
175833Bart7/31/2024Shift 1EndP23329164
174913Jon7/31/2024Shift 2StartP11367492
174913Jon7/31/2024Shift 2StartP31186612
174913Jon7/31/2024Shift 2StartP23329164
175422Peter7/31/2024Shift 3StartP11374583
175422Peter7/31/2024Shift 3StartP31194139
175422Peter7/31/2024Shift 3StartP23351105
174913Jon7/31/2024Shift 2EndP11374583
174913Jon7/31/2024Shift 2EndP31194139
174913Jon7/31/2024Shift 2EndP23351105
175422Peter7/31/2024Shift 3EndP11423881
175422Peter7/31/2024Shift 3EndP31234736
175422Peter7/31/2024Shift 3EndP23351105

 

@amitchandak @VijayPerepa 

Labels:
Message 1 of 9
1,018 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to harshagraj

‎10-10-2024 07:46 PM

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
    )

vyiruanmsft_0-1728614788080.png

Best Regards

View solution in original post

Difference in Meter Reading Calculation based on dates(Updated).pbix
Message 6 of 9
927 Views
8 REPLIES 8
Greg_Deckler
Community Champion
Community Champion

‎10-09-2024 09:23 AM

@harshagraj I don't see a Shift 3 End on 7/29/2024. So are you supposed to use the Start for Shift 3 when End is selected? A bit confused.

"If I select the End then considering July 29, 2024 it should calculate differnce as Shift3(July 29,2024)-Shift1(July 30,2024) at Shift1 (July 30,2024)"



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 9
994 Views
0
harshagraj
Post Partisan
Post Partisan
In response to Greg_Deckler

‎10-09-2024 11:07 PM

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 IDUser NameReadingCaptureOnShift NameShiftStartOrEndInputValue
174913Jon7/28/2024Shift 3StartP12244567
174913Jon7/28/2024Shift 3StartP32344567
174913Jon7/28/2024Shift 3StartP21962234
175833Bart7/29/2024Shift 1StartP12943326
175833Bart7/29/2024Shift 1StartP32989733
175833Bart7/29/2024Shift 1StartP23042042
175833Bart7/29/2024Shift 2EndP12281906
175833Bart7/29/2024Shift 2EndP32823592
175833Bart7/29/2024Shift 2EndP22969746
174913Jon7/29/2024Shift 2StartP12782375
174913Jon7/29/2024Shift 2StartP32982522
174913Jon7/29/2024Shift 2StartP23020714
174913Jon7/29/2024Shift 2EndP12802801
174913Jon7/29/2024Shift 2EndP32605439
174913Jon7/29/2024Shift 2EndP22906316
174557Wan7/29/2024Shift 2StartP12632718
174557Wan7/29/2024Shift 2StartP32272392
174557Wan7/29/2024Shift 2StartP22941521
174557Wan7/29/2024Shift 3StartP12683621
174557Wan7/29/2024Shift 3StartP32982543
174557Wan7/29/2024Shift 3StartP22894587
174557Wan7/30/2024Shift 3EndP11335543
174557Wan7/30/2024Shift 3EndP31160300
174557Wan7/30/2024Shift 3EndP23326923
175833Bart7/30/2024Shift 1StartP11206540
175833Bart7/30/2024Shift 1StartP31050958
175833Bart7/30/2024Shift 1StartP23326974
175833Bart7/30/2024Shift 1EndP11242837
175833Bart7/30/2024Shift 1EndP31081205
175833Bart7/30/2024Shift 1EndP23326924
174913Jon7/30/2024Shift 2StartP11242837
174913Jon7/30/2024Shift 2StartP31081205
174913Jon7/30/2024Shift 2StartP23326924
174557Wan7/30/2024Shift 3StartP11282022
174557Wan7/30/2024Shift 3StartP31115002
174557Wan7/30/2024Shift 3StartP23326924
174913Jon7/30/2024Shift 3EndP11282022
174913Jon7/30/2024Shift 3EndP31115002
174913Jon7/30/2024Shift 3EndP23326924
174913Jon7/30/2024Shift 2EndP11282022
174913Jon7/30/2024Shift 2EndP31115002
174913Jon7/30/2024Shift 2EndP23326924
175833Bart7/31/2024Shift 1StartP11335543
175833Bart7/31/2024Shift 1StartP31160300
175833Bart7/31/2024Shift 1StartP23326924
175833Bart7/31/2024Shift 1EndP11367492
175833Bart7/31/2024Shift 1EndP31186612
175833Bart7/31/2024Shift 1EndP23329164
174913Jon7/31/2024Shift 2StartP11367492
174913Jon7/31/2024Shift 2StartP31186612
174913Jon7/31/2024Shift 2StartP23329164
175422Peter7/31/2024Shift 3StartP11374583
175422Peter7/31/2024Shift 3StartP31194139
175422Peter7/31/2024Shift 3StartP23351105
174913Jon7/31/2024Shift 2EndP11374583
174913Jon7/31/2024Shift 2EndP31194139
174913Jon7/31/2024Shift 2EndP23351105
175422Peter7/31/2024Shift 3EndP11423881
175422Peter7/31/2024Shift 3EndP31234736
175422Peter7/31/2024Shift 3EndP23351105
175833Bart7/29/2024Shift 1EndP12420065
175833Bart7/29/2024Shift 1EndP32734853
175833Bart7/29/2024Shift 1EndP22502956
174913Jon7/29/2024Shift 1EndP12298965
174913Jon7/29/2024Shift 1EndP32584894
174913Jon7/29/2024Shift 1EndP22713852
175833Bart7/29/2024Shift 3EndP12659880
175833Bart7/29/2024Shift 3EndP32595066
175833Bart7/29/2024Shift 3EndP22302885
174913Jon7/29/2024Shift 3EndP12853893
174913Jon7/29/2024Shift 3EndP32771344
174913Jon7/29/2024Shift 3EndP22429925
Message 3 of 9
960 Views
0
Anonymous
Not applicable
In response to harshagraj

‎10-10-2024 02:10 AM

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
    )

vyiruanmsft_0-1728551397322.png

Best Regards

Difference in Meter Reading Calculation based on dates.pbix
Message 4 of 9
950 Views
0
harshagraj
Post Partisan
Post Partisan
In response to Anonymous

‎10-10-2024 02:42 AM

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



harshagraj_2-1728552534321.png

 

Message 5 of 9
944 Views
0
Anonymous
Not applicable
In response to harshagraj

‎10-10-2024 07:46 PM

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
    )

vyiruanmsft_0-1728614788080.png

Best Regards

Difference in Meter Reading Calculation based on dates(Updated).pbix
Message 6 of 9
928 Views
harshagraj
Post Partisan
Post Partisan
In response to Anonymous

‎10-17-2024 02:47 AM

Hello @Anonymous waiting for your response.

Message 9 of 9
801 Views
0
harshagraj
Post Partisan
Post Partisan
In response to Anonymous

‎10-16-2024 03:51 AM

Hello @Anonymous  sorry that this is working only for a matrix when all those columns are available. But if i have to make some card visual out of it its becoming blank or an column chart.

Message 8 of 9
818 Views
0
harshagraj
Post Partisan
Post Partisan
In response to Anonymous

‎10-15-2024 02:13 AM

Hi @Anonymous  thanks a lot. This is working

Message 7 of 9
853 Views
0

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

Check out the April 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All