Forum Discussion

IEPMost's avatar
IEPMost
Icon for Helper III rankHelper III
5 years ago
Solved

How to create a measure for subtracting two values from two different dates?

Hi guys,   I have two columns [Date] and [meter reader] for 2019 and 2020! In order to compare the heat consumption I have two subtract two values from the same column [meter reader] for certain da...
  • Anonymous's avatar
    Anonymous
    5 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:

    Here is the pbix file.

     

    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