using slicer date value in dax formula

[kaileena]

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 · Accepted solution]

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]

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]

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.