Unable to use SELECTEDVALUE variable in DAX calculations

[Gondal-756]

Hi,
I have a DAX calculation where I use a slectedvalue()  from a month slicer.
If I just return the selectedvalue() itself,  it returns the selected month from slicer, but when I try to use this selectedvalue in a DAX condition, it never works.


My Data:

1. Calendar Table :

2. Sales Table:

What I Want:


Sum the sales for each month , but only upto the selected month (in month slicer).
For example if I select February, I should see total sales of January and February only.

My current DAX Code :

Sales by Selected Month = 
VAR SelectedMonth = SLECTEDVALUE('Calendar'[date].[Month])

RETURN
CALCULATE(
           SUM('Table'[Sales(USD)]), 
           FILTER(
                 'Table',
                  'Table'[date].[Month] <= SelectedMonth
                  )
 )   

In the above Dax code, in the calculate function instead of using SlectedMonth variable, if I use a static month number like 2, it works but when I use the variable it returns the sum of sales for the whole year.

@all

[Gondal-756]

Hi @gmsambornt Thanks a lot, for your efforts.  

I have following questions:

1. Is it must to have a separate slicer table? Can I just use the Date table for slicer directly?

2. In my dashboard, I would use the month slicer for other visuals as well. Since this is a different application of slicer, I can't keep the slicer interaction enabled with this visualization. I want to use the selected slicer month value indirectly using Dax.

In your solution, when I disable the interaction of slicer with visualization, nothing works and it shows all the data. Can we somehow make it work while keeping the interaction disabled?

[gmsamborn · Accepted solution]

Hi @Gondal-756 

It wasn't easy to make the slicer based on the calendar table AND have the visual display rows that AREN'T from the selected month.

I based the rows on the values in the table that was originally set up as a disconnected slicer. (renamed to 'MonthList')

The measure is as follows:

xInnerCalc = 
VAR _Dcurr = MAX( 'Date'[YearMonth] )
VAR _Curr = SELECTEDVALUE( 'MonthList'[YearMonth] )
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[YearMonth],
            "__Amt",
                SUM( 'Sales'[Sales(USD)] )
        ),
        [YearMonth] = _Curr
    )
VAR _Total =
    SUMX(
        _Table,
        [__Amt]
    )
VAR _Result =
    IF(
        _Curr <= _Dcurr,
        _Total
    )
RETURN
    _Result

To handle the totals, I created the following measure.

xSales = 
    SUMX(
        'MonthList',
        [xInnerCalc]
    )

Let me know if this helps.

Gondal-756 - 2.pbix

[gmsamborn]

Hi @Gondal-756 

In my solution, I used a date table as well as a disconnected slicer table.  

1. Create a well-formed Date table.  The date table you listed isn't a date table.  (Even if you aren't using time intelligence calculations, it is a best practice to use a well-formed date table.)

In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

• All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. 
• There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
• The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

2. Make sure your ‘Sales’[Date] has a Date data-type, not DateTime.  If you need the time portion, duplicate the DateTime column and change to Date.  Rename the columns accordingly.

3. Create a 1:* (one direction) relationship between ‘Date’[Date] and ‘Sales’[Date].

4. Create a table that is a list of [MMM-YY] and [YearMonth] from ‘Date’ to be used in a slicer.  ( [Month] and [MonthNo] could be used if you prefer.  My preference would be to handle multiple years.)

SlicerMonths = 
    SUMMARIZE(
        'Date',
        'Date'[MMM-YY],
        'Date'[YearMonth]
    )

5. Create the following measure:

Sales by Selected Month = 
VAR _SelectedMonth = MAX( 'SlicerMonths'[YearMonth] )
RETURN
    CALCULATE(
        SUM( 'Sales'[Sales(USD)] ),
        FILTER(
            ALL( 'Date'[YearMonth] ),
            'Date'[YearMonth] <= _SelectedMonth
        )
    )

Let me know if you have any questions.

Gondal-756.pbix