Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
It seems that you may just use one SWITCH Function.
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,"")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |