selectedvalue dax question

[SDPowerBI123]

i have a simple what if parameter generating a list of numbers like this:

Numbers of Months = GENERATESERIES(3, 26, 1),

and then i have a selectedvalue dax measure that captures what is being selected like this:

Numbers of Months Value = SELECTEDVALUE('Numbers of Months'[Numbers of Months])

i put this measure in a card, and link the column to a slicer, when i selected lets say 15, the card with the measure inside says 15 no problem.

the problem is when i try to use this selectedvalue dax in another measure like this one:

Open X Months Prior =

var PriorDate = DATEADD('O 29 Years Monthly'[Date], -'Numbers of Months'[Numbers of Months Value], MONTH)

RETURN

CALCULATE(

    SUM('O 29 Years Monthly'[Open]),

    ALL('O 29 Years Monthly'),

    'O 29 Years Monthly'[Date] = PriorDate

)

so long story short i am trying to do some data analysis on some stock, and i try to discount the months back, i assume that measure would work in that second measure, but it just returns blank. can any one help me to understand why its returning blank when the card clearly says its returning a number? thanks.

i put link below.

https://www.dropbox.com/scl/fi/ji0qg4h6z3l40eizw9o57/O-X-Number-Of-Month-Growth.pbix?rlkey=6m1hw03ps...

[connect · Accepted solution]

Hi there, 

You need to understand that SELECTEDVALUE () DAX function returns a scalar value while DATEADD () Returns a table. Also, pay attention to the filter context over here. 

Lets understand it a bit futher!

Why It Returns Blank?

The DATEADD function, along with other time intelligence functions, expects a column with dates as input, not a scalar value like what you get from SELECTEDVALUE. When you pass -‘Numbers of Months’[Numbers of Months Value] directly to DATEADD, it's possible that the context is not correctly understood, resulting in an invalid operation and hence returning blank.

What can you do as follows?

Modify the Measure
Open X Months Prior =
VAR SelectedMonths = SELECTEDVALUE('Numbers of Months'[Numbers of Months]) // Get the selected number of months
VAR PriorDate =
CALCULATETABLE(
DATEADD('O 29 Years Monthly'[Date], -SelectedMonths, MONTH),
ALL('O 29 Years Monthly')
)
RETURN
CALCULATE(
SUM('O 29 Years Monthly'[Open]),
FILTER(
ALL('O 29 Years Monthly'),
'O 29 Years Monthly'[Date] IN PriorDate
)
)

I hope this will work for you!! 

[DataNinja777]

Hi @SDPowerBI123 ,

Modifying your measure to ensure that the PriorDate is calculated in a context where [Numbers of Months Value] has a defined value and is used correctly will resolve the issue of the blank card value.

Open X Months Prior =
VAR SelectedMonths = [Numbers of Months Value]
VAR PriorDate = 
    CALCULATE(
        MAX('O 29 Years Monthly'[Date]),
        DATEADD('O 29 Years Monthly'[Date], -SelectedMonths, MONTH)
    )
RETURN
CALCULATE(
    SUM('O 29 Years Monthly'[Open]),
    FILTER(
        ALL('O 29 Years Monthly'),
        'O 29 Years Monthly'[Date] = PriorDate
    )
)

 Best regards,

[SDPowerBI123]

gotten this error afterwards.

[Irwan]

hello @SDPowerBI123 

i dont find any issue with your DAX.

'Open X Months Prior' is a column and the value is shown inside the column (i assumed the value is expected to be right), so if your DAX would show no value or error value.

the only thing that i could find is, you dont create relationship between 'Number of Months' table and 'O 29 Years Monthly'.

You take month number in your slicer from 'Number of Months' table, while you use 'Open X Months Prior' as your value in bar chart. However there is no relationship between those two table.

first i create a calculated column to get month number in 'O 29 Years Monthly'.

then i use that number as a relationship between those two table.

and your calculation is shown automatically.

However, this is just an example because the calculated column that i made is not matched to your calculation.

Basically the issue might be from relationship so you need to seek where is the relationship between those two tables.

Hope this will help.

Thank you.

[SDPowerBI123]

the numbers of months table column of number isn't refering to a month number particularily, but it is rather the count of numbers of months to discount, so 1 in that column doesn't refer to january, it refers to numbers of months to discount.