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.
