Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Table Slicer based on dates period and returns dates

I have a Date table and a FactSales table and their relationship is Date[date] and FactSales[Date]. I have a measure that sum sales e.g Total sales = SUM(FactSales[sales]). I have a table called Relation.

_______________________________________________________________________
Match                    | TypeID | Type           | No  |Datepartname |
________________________________________________________________________
DEFAULT - Year on Year   | 1      |Userelationship | NA  | NA          |
Previous 1 Week          | 3      | Dateadd        | 1   | Week        |
Previous 2 Week          | 3      | Dateadd        | 2   | Week        |
Previous 3 Week          | 3      | Dateadd        | 3   | Week        |
Previous 12 Week         | 3      | Dateadd        | 12  | Week        |
------------------------------------------------------------------------

This Relation[Match] column will be used as a slicer that will filter through the date table to get the Total Sales based on what is selected.

I am struggling in making this work by using the DAX below: First I need to identify the current date by using the variable _maxDate

measure = 
Var _maxDate = CALCULATE ( MAX ( Date[Dates] ), ALLSELECTED ( Date ) )   --- current's date
var _selection = SELECTEDVALUE(Relation[Match] )
RETURN
SWITCH(TRUE(),
        _selection = "DEFAULT - Year on Year", "DEFAULT - Year on Year",
        _selection = "Previous 1 Week", DATEADD(Date[Dates],-7,DAY),
        _selection = "Previous 2 Week”, DATEADD(Date[Dates],-14,DAY),
        _selection = "Previous 3 Week", DATEADD(Date[Dates],-21,DAY),
        _selection = "Previous 12 Week", DATEADD(Date[Dates],-84,DAY),
        BLANK()
)

My Dax return error.

Expected outcome:

Whenever, Previous 1 week is selected on the slicer, it should return the previous week date and when Previous 2 week is selected, it should return 2 weeks ago date and so on. If this is achieved, then the measure should automatically be computed based on what is selected on the slicer.

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Week in DATEADD function do not work and there is a Red squiggly line appeared on underline and underneath week

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

      Sorry, that was a mistake on my part. Can you try the following measure?

      measure = 
var _selection = SELECTEDVALUE(Relation[No])
RETURN
    IF(
        _selection IN {1,2,3,12},
        CALCULATE(
            [Total Sales],
            DATEADD(Date[Dates],-_selection * 7,DAY)
        )
    )

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Fowmy Thanks for the reply. I would like to validate my date if it returns the right date. So i decided to use 

        Check Date returned = 

var _selectionNo = SELECTEDVALUE(SelectedTbl[No] )

RETURN
 IF(
        _selectionNo IN {"1","2","3","4","8","12"} ,
            DATEADD(DIMDateTbl[Date],-_selectionNo * 7,DAY), BLANK()
        )

        It is giving error which state -
        Error Message: MdxScript(Model) (87, 21) Calculation error in measure 'Date'[Check Date returned]: A table of multiple values was supplied where a single value was expected.
        please how can i resolve this to return date whenever the slicer is selected. This is to check if my result returns the right date.

        Thanks