Forum Discussion
selectedvalue dax question
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!!
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.
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.