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

[IEPMost]

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 days. In my visuals there are three slicers for quarter, month and calendar week;

If I choose october as a month, the measure should subtract the value from meter reader for 1st of october from the value from meter reader for 30th october (both for 2019 and 2020) and if I choose calender week 37, the measure should subtract the value from meter reader for 9th september 2019 from the value from meter reader for 13th september 2019 (for 2020 it would be 7th and 11th september). I hope you guys know what I mean.

Here is my .pbix file!

https://www.dropbox.com/s/3f59dxnsungyipy/Test.pbix?dl=0

If you want more information, please reach out.
Thanks!!

[v-eqin-msft · Accepted solution]

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

[amitchandak]

@IEPMost , with help from date table you can use year behind table

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Or

Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

Or you need to have different for different period

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.