cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamprajot Established Member
Established Member

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

 

2 REPLIES 2
iamprajot Established Member
Established Member

Re: Please Correct This DAX !

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,"")

Community Support Team
Community Support Team

Re: Please Correct This DAX !

@iamprajot,

 

It seems that you may just use one SWITCH Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 151 members 1,540 guests
Please welcome our newest community members: