Compare measure value over two dates

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 ) )

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.

 

 

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 is this what you want assuming Sep 21st is selected?

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

Example output:

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 )
        ) 
    )

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] )
        )
    )

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.