Forum Discussion

iamprajot's avatar
iamprajot
Icon for Responsive Resident rankResponsive Resident
8 years ago

Please Correct This DAX !

I want Previous (Year/Quarter/Month/Day) Amounts for Current (Year/Quarter/Month/Day) Amounts

So I created separate Measures and put them in a Switch function based on selection or Year/Quarter/Month/Day.

 

YY = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,YEAR))
QQ = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,QUARTER))
MM = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,MONTH))
DD = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,DAY))

 

Previous =
VAR Selected = SWITCH(TRUE(),
ISFILTERED(Data[OrderDate].[Year]),"YEAR",
ISFILTERED(Data[OrderDate].[Quarter]),"QUARTER",
ISFILTERED(Data[OrderDate].[Month]),"MONTH",
ISFILTERED(Data[OrderDate].[Day]),"DAY","Nothing")
RETURN
SWITCH(TRUE(),

Selected="Year",[YY],

Selected="Quarter",[QQ],

Selected="Month",[MM],

Selected="Day",[DD],

"")

 

What I want is to put these separate calculation Measures inside the Switch function rather then calling them from outside, like

Previous = 
VAR Selected = SWITCH(TRUE(),
ISFILTERED(Data[OrderDate].[Year]),"YEAR",
ISFILTERED(Data[OrderDate].[Quarter]),"QUARTER",
ISFILTERED(Data[OrderDate].[Month]),"MONTH",
ISFILTERED(Data[OrderDate].[Day]),"DAY","Nothing")
RETURN
SWITCH(TRUE(),

Selected="Year",CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,YEAR)),

Selected="Quarter",CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,QUARTER)),

Selected="Month",CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,MONTH)),

Selected="Day",CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,DAY)),

"")

 

But Calculate comes after RETURN and I want them inside the Switch function so

RETURN

SWITCH ..... (CALCULATE.....

 

RETURN

CALCULATE

 

Need Help

2 Replies

  • iamprajot's avatar
    iamprajot
    Icon for Responsive Resident rankResponsive Resident
    8 years ago

    I put them too in Variables which I tried earlier too but it worked this time and not that time.

    Still, open for Suggestions...

     

    Previous =
    VAR Selected = SWITCH(TRUE(),
    ISFILTERED(Data[OrderDate].[Year]),"YEAR",
    ISFILTERED(Data[OrderDate].[Quarter]),"QUARTER",
    ISFILTERED(Data[OrderDate].[Month]),"MONTH",
    ISFILTERED(Data[OrderDate].[Day]),"DAY","Nothing")
    VAR YY = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,YEAR))
    VAR QQ = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,QUARTER))
    VAR MM = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,MONTH))
    VAR DD = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,DAY))
    RETURN
    SWITCH(TRUE(),Selected="Year",YY,Selected="Quarter",QQ,Selected="Month",MM,Selected="Day",DD,"")