Forum Discussion

kaileena's avatar
kaileena
Frequent Visitor
4 years ago
Solved

using slicer date value in dax formula

Hello,   I am unable to use the date selected from the slicer into my dax formula. The value returned is blank. Note that my 'date' column is called "full formar for period". I created a continuou...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi kaileena 

    in this case SELECTEDVALUE returns blank as it cannot find a single value of the date because you selecting a period (a month or multiple months) not a single date. Therefore as long as there is no blank dates in the Measurement Table, the filtered table will be empty. Therefore you need to use ALLSELECTED or VALUES to retrieve the selected dates, then you can use IN operator to check if current row date inside CALCULATE filter is within the selected period. Please try the following with either ALLSELECTED or VALUES

    Value for Slicer Selected Month Budget Spent =
CALCULATE (
    SUM ( 'Measurement'[Value] ),
    'Measurement'[Type] = "Budget Spent",
    Measurement[Full Format for Period]
        IN VALUES ( ForSlicer[Full Format for Period] )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi kaileena 

in this case SELECTEDVALUE returns blank as it cannot find a single value of the date because you selecting a period (a month or multiple months) not a single date. Therefore as long as there is no blank dates in the Measurement Table, the filtered table will be empty. Therefore you need to use ALLSELECTED or VALUES to retrieve the selected dates, then you can use IN operator to check if current row date inside CALCULATE filter is within the selected period. Please try the following with either ALLSELECTED or VALUES

Value for Slicer Selected Month Budget Spent =
CALCULATE (
    SUM ( 'Measurement'[Value] ),
    'Measurement'[Type] = "Budget Spent",
    Measurement[Full Format for Period]
        IN VALUES ( ForSlicer[Full Format for Period] )
)