Forum Discussion
harshagraj
1 year agoPost Partisan
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...
- Anonymous1 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
Anonymous
1 year agoNot applicable
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
harshagraj
1 year agoPost Partisan
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.