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 continuous date column which i called "full format for period"

 

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

 

 

 

 

 


 

  • 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] )
)

3 Replies

  • 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] )
)
  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    4 years ago
    Hi! Try to remove some parts from formula:
    Value for Slicer Selected Month Budget Spent =
    CALCULATE(
    SUM('Measurement'[Value]),
    'Measurement'[Type] IN { "Budget Spent" }
    )
     
    • kaileena's avatar
      kaileena
      Frequent Visitor
      4 years ago

      i did. i think the problem comes from how my tables are connected. (Or not connected. )

       

      This is the current outcome.it does not show the correct value. i will explain.

       

      this is the model. The value i need to aggregate is in the table "measurement" which has a column called "full format for period". For a different use case, i created the "ForSlicer" table (

      ForSlicer = VALUES('Measurement'[Full Format for Period]) ). Of course the "ForSlicer" table is used in the Slicer.
       
      I did not connect "ForSlicer" with the "measurement" table because it does not suit my previous use case. Previous use case needs to have the two tables not connected. Previous use case show the last 12 months wrt to slicer selection. So i guess the value 2.61 is the sum of budget for the 12 months selected.