Date logic in DAX

0
votes

I am trying to compute a percentage difference between two values - market index levels separated by a period of time (the period will be determined by user input in a Power BI Slicer tool). I don't understand how I can cross reference values DAX uses by the associated date.

Value % difference from Value = 
VAR __BASELINE_VALUE = SUM('Equity Markets (2)'[Value])
VAR __VALUE_TO_COMPARE = SUM('Equity Markets (2)'[Value])
RETURN
    IF(
        NOT ISBLANK(__VALUE_TO_COMPARE),
        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
    )

"Value" is a column in a table "Equity Markets (2)" the table also includes a "Date" column.

What is the syntax for selecting a value from Value based on an associated date?

Apologies for asking such a basic question - feels like 30 sec of googling should have done it for me.

The slicer is engaging with the bar graph correctly - I know becouse I'm measuring the levels. I think all the % changes are zero because I'm evaluating x/x -1

my relationships

1
datesyntaxpowerbidaxpercentage
Can you share your relationships? Also, how exactly you are using slicer for user input? is it on the very same date column? – Pratik Bhavsar
Thank you for replying, question adjusted as you suggested. – Tikhon

1 Answers

0
votes
percentage change = 


VAR
__EarliestValue = CALCULATE(SUM('Equity Markets (2)'[Value]),
           FIRSTDATE('Equity Markets (2)'[Date]))

VAR __LastDateValue = CALCULATE(SUM('Equity Markets (2)'[Value]),
            LASTDATE('Equity Markets (2)'[Date]))
RETURN

CALCULATE(
    DIVIDE(__LastDateValue,__EarliestValue)-1)