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: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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
359 Views
0
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support
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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Difference in Meter Reading Calculation based on dates(Updated).pbix
Message 6 of 9
268 Views
8 REPLIES 8
Greg_Deckler
Super User
Super User

‎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!: Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 9
335 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
301 Views
0
v-yiruan-msft
Community Support
Community Support
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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Difference in Meter Reading Calculation based on dates.pbix
Message 4 of 9
291 Views
0
harshagraj
Post Partisan
Post Partisan
In response to v-yiruan-msft

‎10-10-2024 02:42 AM

Hello @v-yiruan-msft  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
285 Views
0
v-yiruan-msft
Community Support
Community Support
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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Difference in Meter Reading Calculation based on dates(Updated).pbix
Message 6 of 9
269 Views
harshagraj
Post Partisan
Post Partisan
In response to v-yiruan-msft

‎10-17-2024 02:47 AM

Hello @v-yiruan-msft waiting for your response.

Message 9 of 9
142 Views
0
harshagraj
Post Partisan
Post Partisan
In response to v-yiruan-msft

‎10-16-2024 03:51 AM

Hello @v-yiruan-msft  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
159 Views
0
harshagraj
Post Partisan
Post Partisan
In response to v-yiruan-msft

‎10-15-2024 02:13 AM

Hi @v-yiruan-msft  thanks a lot. This is working

Message 7 of 9
194 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All