Dynamic calculated table or visualization based on two selected dates

Hi Anonymous,

Create a calendar table which is unrelated to fact data table. Like this:

Date Tab = CALENDAR(DATE(2018,1,1),DATE(2018,1,8)) 

New measures:

value1 =
VAR maxdate =
    CALCULATE ( MAX ( TestData[Date] ), ALL ( TestData[Date] ) )
VAR mindate1 =
    MIN ( 'Date Tab'[Date] )
VAR mindate2 =
    CALCULATE (
        MAX ( TestData[Date] ),
        FILTER ( ALL ( TestData ), TestData[Date] < maxdate )
    )
VAR cal1 =
    CALCULATE (
        SUM ( TestData[Value] ),
        FILTER ( TestData, TestData[Date] = mindate1 )
    )
VAR cal2 =
    CALCULATE (
        SUM ( TestData[Value] ),
        FILTER ( TestData, TestData[Date] = mindate2 )
    )
RETURN
    IF ( ISFILTERED ( 'Date Tab'[Date] ), cal1, cal2 )

value2 =
VAR maxdate1 =
    CALCULATE ( MAX ( TestData[Date] ), ALL ( TestData[Date] ) )
VAR maxdate2 =
    MAX ( 'Date Tab'[Date] )
VAR cal1 =
    CALCULATE (
        SUM ( TestData[Value] ),
        FILTER ( TestData, TestData[Date] = maxdate1 )
    )
VAR cal2 =
    CALCULATE (
        SUM ( TestData[Value] ),
        FILTER ( TestData, TestData[Date] = maxdate2 )
    )
RETURN
    IF ( ISFILTERED ( 'Date Tab'[Date] ), cal2, cal1 )

diff = [value2]-[value1]

Average Diff = AVERAGEX(VALUES(TestData[Hour]),[diff])

The highlighted Matrix (put [Hours] into Columns section of Matrix, put [Average Diff] into values section) is your desired output. The last column is the default total column by Matrix, it shows the average of values across all hours, but its column name cannot be changed. If you don't want it, you can use a Table visual, as shown in above image, at the bottom the highlighted Matrix.

Best regards,

Yuliana Gu