Compare measure value over two dates

[powermoss]

I am trying to create a table or matrix which compares measure values on two dates - yesterday to a user-selected second date.

In Excel this is a lookup column to a tale containing yesterday's data, and a second lookup column to a table containing the selected date's data.  In Power Query my data is all in one table.  

I am struggling to have the dates show in the matrix visual as headers, and to have the two values - yesterday's and the selected date's - display in different columns.

Here is a visual of what I'm hoping to achieve.

I already have measures in place for "Yesterday", e.g.

Yesterday's Forecast Rev Commit = CALCULATE(
    SUM('Table_data'[Forecast Rev Commit]),
    'Table_data'[File Date] = TODAY() -1
)

Thanks for any help.

[parry2k · Accepted solution]

@powermoss here is the previous file with the updated measure and it is working, since it doesn't have today's data it is not showing that date but if you put today's data it will work - today's vs selected date

[powermoss]

Thank you so much @parry2k - it's working beautifully and I wouldn't have got there without the help. Much appreciated. 👏🏻

[powermoss]

@parry2k you're awesome for helping on this.

That new one seems to have scuppered things somewhat.  I've updated the dataset and published it again here.

I'm stumped on how to get just two columns of data to show: the date chosen from a slicer, and Today's date. I've tried a few FILTER arguments myself, and wasn't aware of the TREATAS function, but I can't seem to limit my results to only two columns when there is a date range between TODAY and the selected value.

[parry2k]

@powermoss try something like this:

One Measure = 
VAR __CurrentDate = TODAY () 
VAR __SelectedDate =  MAX ( 'File Date'[File Date] ) 
VAR __DatesTable = { __SelectedDate, __CurrentDate }
RETURN
    CALCULATE ( 
        SUM('Table_data'[Value]),
        KEEPFILTERS (
            TREATAS ( __DatesTable, 'Date'[Date] )
        )
    )

[powermoss]

That is awesome! Thank you @parry2k.

Can I ask about the One Measure - now that I've shown an end user, there's a change to the dates they want to use. 🙄  One of the columns should always be TODAY and the other should be the date chosen in the drop down.  I've tried to modify what you've got there in the __PreviousDate variable by trying

__PreviousDate = SELECTEDVALUE('File Date'[File Date])

but what ends up happening to my matrix is that I just get more and more columns, one for every day inbetween yesterday and the selected date. (Interestingly, I can't get the values from today to appear in a column)

I tried get a numerical answer to populate the measure, but no luck.  I realize this looks horrible, but I thought it might get me there as inelegant as it is.

oneMeasure2 =
VAR __CurrentDate = TODAY()

VAR __dateValue = 
    VAR startDate = SELECTEDVALUE('fileDateTable'[File Date])
    VAR endDate = TODAY()
RETURN DATEDIFF(startDate, endDate, DAY)

VAR __PreviousDate = SELECTEDVALUE('fileDateTable'[File Date]) - __dateValue

RETURN
    CALCULATE ( 
       SUM('Table Data'[Value]),
       KEEPFILTERS (
            DATESBETWEEN ( 'Date'[Date], __PreviousDate, __CurrentDate )
        ) 
    )

Thanks!

[parry2k]

@powermoss I have put together the solution in the attached file, something that is scalable, and only one measure is required.

Example output:

[parry2k]

@powermoss is this what you want assuming Sep 21st is selected?

[powermoss]

@parry2k yes that looks like what I'm after. Assume the user selects 21-Sept, a value of the measure would appear in two columns - one for 21-Sept and one for yesterday (24-Sept). 
I think what you've done above is take off the Switch values to rows option on the matrix?  I was hoping to have that on, as I will be putting a long list of measures (approx. 20) into the visual, and if I take that option off the user has to scroll a long way left and right to see each measure's value.  Hopefully the picture I put in the .pbix was there, but just in case, this is the desired result:

[parry2k]

@powermoss I'm not able to wrap my head around what you are trying to do here, I think because of lack of explanation it is hard to provide a solution. It will be easier if you put some sample data and the expected output, preferably share pbix file without sensitive information.

[powermoss]

Thanks @parry2k.  I guess I can't attach the file directly here; here's a link to a a simple dataset that hopefully is a good show and tell. Table Compare Sample 

[powermoss]

Thanks @parry2k , I appreciate that - anything to make my model run smoother is great!  I do alrelady have a date table in the overall model, marked as a date table. The above measure keeps me at a one-day separation from my selected date though, and I am hoping to have the user be able to always compare yesterday's values to whatever date they choose.  

Here's what my matrix is shaping up to look like.  In this visual, at the moment items are calculated as:

• Sum of Forecast Rev Commit is just the column pulled in as a measure

Selected Date Commit Fcst Rev = CALCULATE(
    SUM('Table_data'[Forecast Rev Commit]),FILTER('Table_data','Table_data'[File Date]=SELECTEDVALUE('Table_data'[File Date])))

Yesterday's Commit Fcst Rev = CALCULATE(
    SUM('Table_data'[Forecast Rev Commit]),
    'Table_data'[File Date] = TODAY() -1
)

Yesterday's Commit Fcst Rev (parry2k) = CALCULATE(
    SUM('Table_data'[Forecast Rev Commit]),
    DATEADD('Date'[Date], -1, DAY)
)

So I think I can get a reasonable table using my 'Yesterday' measure and SELECTEDVALUE, but I can't get them to show on a single row - any thoughts there?  I would ideally have each measure on one row, DAX to have Power BI display multiple values of a single measure on one row is more than I can come up with.  (Especially as I would want to display the difference between the two as a third value on that row.

[parry2k]

@powermoss 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

You measure for yesterday will be something like this after date dimension is added to the model

Previous Day = 
CALCULATE ( [Your measure], DATEADD( 'Date Table'[Date], -1, DAY ) )