Forum Discussion

powermoss's avatar
powermoss
Regular Visitor
2 years ago
Solved

Compare measure value over two dates

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 da...
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    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's avatar
powermoss
Regular Visitor
2 years ago

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!