Forum Discussion
How to create a measure for subtracting two values from two different dates?
- Anonymous5 years ago
Hi IEPMost ,
Let's take Week Slicer as an example, according to my understand, when you choose Week37 , then calculate 9/11/2020 minus 9/7/2020 and 9/13/2019 minus 9/9/2020 respectively ,right?
2020 Week Measure = VAR _seleWeek = SELECTEDVALUE ( 'Date'[Woche des Jahres] ) VAR _minDay = CALCULATE ( MIN ( 'meter reader 2020'[TST] ), FILTER ( ALL ( 'Date' ), 'Date'[Woche des Jahres] = _seleWeek ) ) VAR _maxDay = CALCULATE ( MAX ( 'meter reader 2020'[TST] ), FILTER ( ALL ( 'Date' ), 'Date'[Woche des Jahres] = _seleWeek ) ) RETURN CALCULATE ( MAX ( 'meter reader 2020'[Kunde017(kWh)] ), FILTER ( 'meter reader 2020', 'meter reader 2020'[TST] = _maxDay ) ) - CALCULATE ( MAX ( 'meter reader 2020'[Kunde017(kWh)] ), FILTER ( 'meter reader 2020', 'meter reader 2020'[TST] = _minDay ) )Or use RANK()
rank = RANKX ( FILTER ( ALL ( 'Date' ), 'Date'[Woche des Jahres] = SELECTEDVALUE ( 'Date'[Woche des Jahres] ) && YEAR ( 'Date'[TST] ) = YEAR ( MAX ( 'Date'[TST] ) ) ), CALCULATE ( MAX ( ( 'Date'[TST] ) ) ), , ASC )2019 Week Measure = VAR _maxDay = MAXX ( FILTER ( 'Date', YEAR ( 'Date'[TST] ) = 2019 ), [rank] ) RETURN CALCULATE ( SUM ( 'meter reader 2019'[Kunde017(kWh)] ), FILTER ( 'Date', [rank] = _maxDay ) ) - CALCULATE ( SUM ( 'meter reader 2019'[Kunde017(kWh)] ), FILTER ( 'Date', [rank] = 1 ) )My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qin
Hi IEPMost ,
Let's take Week Slicer as an example, according to my understand, when you choose Week37 , then calculate 9/11/2020 minus 9/7/2020 and 9/13/2019 minus 9/9/2020 respectively ,right?
2020 Week Measure =
VAR _seleWeek =
SELECTEDVALUE ( 'Date'[Woche des Jahres] )
VAR _minDay =
CALCULATE (
MIN ( 'meter reader 2020'[TST] ),
FILTER ( ALL ( 'Date' ), 'Date'[Woche des Jahres] = _seleWeek )
)
VAR _maxDay =
CALCULATE (
MAX ( 'meter reader 2020'[TST] ),
FILTER ( ALL ( 'Date' ), 'Date'[Woche des Jahres] = _seleWeek )
)
RETURN
CALCULATE (
MAX ( 'meter reader 2020'[Kunde017(kWh)] ),
FILTER ( 'meter reader 2020', 'meter reader 2020'[TST] = _maxDay )
)
- CALCULATE (
MAX ( 'meter reader 2020'[Kunde017(kWh)] ),
FILTER ( 'meter reader 2020', 'meter reader 2020'[TST] = _minDay )
)Or use RANK()
rank =
RANKX (
FILTER (
ALL ( 'Date' ),
'Date'[Woche des Jahres] = SELECTEDVALUE ( 'Date'[Woche des Jahres] )
&& YEAR ( 'Date'[TST] ) = YEAR ( MAX ( 'Date'[TST] ) )
),
CALCULATE ( MAX ( ( 'Date'[TST] ) ) ),
,
ASC
)2019 Week Measure =
VAR _maxDay =
MAXX ( FILTER ( 'Date', YEAR ( 'Date'[TST] ) = 2019 ), [rank] )
RETURN
CALCULATE (
SUM ( 'meter reader 2019'[Kunde017(kWh)] ),
FILTER ( 'Date', [rank] = _maxDay )
)
- CALCULATE (
SUM ( 'meter reader 2019'[Kunde017(kWh)] ),
FILTER ( 'Date', [rank] = 1 )
)My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin