cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Why use a return inside a sumx

Hello, I've seen this pattern to create a measure and I don't understand how we can make a return before the end of the sumx ?

``````Sales PQ :=
IF (
[ShowValueForDates],
SUMX (
VALUES ( ‘Date’[Fiscal Year Quarter Number] ),
VAR CurrentFiscalYearQuarterNumber = ‘Date’[Fiscal Year Quarter Number]
VAR DaysSelected =
CALCULATETABLE (
VALUES ( ‘Date’[Day of Fiscal Quarter Number] ),
REMOVEFILTERS (
‘Date’[Working Day],
‘Date’[Day of Week],
‘Date’[Day of Week Number]
),
‘Date’[DateWithSales] = TRUE
)
RETURN
CALCULATE (
[Sales Amount],
‘Date’[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1,
DaysSelected,
ALLEXCEPT ( ‘Date’, ‘Date’[Working Day], ‘Date’[Day of Week] )
)
)
)``````

1 ACCEPTED SOLUTION
Super User

@GuillaumePower I agree that the construction of the measure is odd and complex. However, without additional information regarding the model, the intended use case, etc. it's hard to know why it was constructed the way it was. It may be that there is a good reason for it or it may be that it could be done much more simply and efficiently.

You can use nested VAR statements in a measure, for example, you could write this:

``````Sales PQ =
VAR __Result =
IF (
[ShowValueForDates],
SUMX (
VALUES ( 'Date'[Fiscal Year Quarter Number] ),
VAR CurrentFiscalYearQuarterNumber = 'Date'[Fiscal Year Quarter Number]
VAR DaysSelected =
CALCULATETABLE (
VALUES ( 'Date'[Day of Fiscal Quarter Number] ),
REMOVEFILTERS (
'Date'[Working Day],
'Date'[Day of Week],
'Date'[Day of Week Number]
),
'Date'[DateWithSales] = TRUE
)
RETURN
CALCULATE (
[Sales Amount],
'Date'[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1,
DaysSelected,
ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] )
)
)
)
RETURN
__Result``````

There are simpler ways to get a previous quarter calculation, such as the following examples:

``````Internet Sales (PQ) =
CALCULATE([Internet Sales],
FILTER(ALL('Dates'),
'Dates'[Date] >= MIN('Dates'[Prior Quarter Date]) &&
'Dates'[Date] <= MAX('Dates'[Prior Quarter Date])))

TI PQ 2 =
CALCULATE(
[Internet Sales],
DATESBETWEEN('Dates'[Date], MIN('Dates'[Prior Quarter Date]), MAX('Dates'[Prior Quarter Date]))
)

NC Intenet Sales (PQ) =
VAR __MinPYDate = MIN('Dates'[Prior Quarter Date])
VAR __MaxPYDate = MAX('Dates'[Prior Quarter Date])
VAR __PQCalendar = CALENDAR(__MinPYDate, __MaxPYDate)
VAR __Table =
SUMMARIZE(
ALL('FactInternetSales'),
'FactInternetSales'[OrderDate],
"__Sales", SUM('FactInternetSales'[SalesAmount]))
VAR __Result = SUMX( FILTER( __Table, [OrderDate] IN __PQCalendar), [__Sales] )
RETURN
__Result
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@GuillaumePower The RETURN statement inside the SUMX function is evaluated for each unique value of ‘Date’[Fiscal Year Quarter Number].

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Resolver I

Why should not more use in this measure a

MyMeasure =

Var TheSum= Sumx(...)

Return  TheSum

How is it possible in one single measure to send many times a value : one for each return, isn't' it ?

More, we have to think too, to the context of the measure, it seems very tricky for me.

Super User

@GuillaumePower I agree that the construction of the measure is odd and complex. However, without additional information regarding the model, the intended use case, etc. it's hard to know why it was constructed the way it was. It may be that there is a good reason for it or it may be that it could be done much more simply and efficiently.

You can use nested VAR statements in a measure, for example, you could write this:

``````Sales PQ =
VAR __Result =
IF (
[ShowValueForDates],
SUMX (
VALUES ( 'Date'[Fiscal Year Quarter Number] ),
VAR CurrentFiscalYearQuarterNumber = 'Date'[Fiscal Year Quarter Number]
VAR DaysSelected =
CALCULATETABLE (
VALUES ( 'Date'[Day of Fiscal Quarter Number] ),
REMOVEFILTERS (
'Date'[Working Day],
'Date'[Day of Week],
'Date'[Day of Week Number]
),
'Date'[DateWithSales] = TRUE
)
RETURN
CALCULATE (
[Sales Amount],
'Date'[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1,
DaysSelected,
ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] )
)
)
)
RETURN
__Result``````

There are simpler ways to get a previous quarter calculation, such as the following examples:

``````Internet Sales (PQ) =
CALCULATE([Internet Sales],
FILTER(ALL('Dates'),
'Dates'[Date] >= MIN('Dates'[Prior Quarter Date]) &&
'Dates'[Date] <= MAX('Dates'[Prior Quarter Date])))

TI PQ 2 =
CALCULATE(
[Internet Sales],
DATESBETWEEN('Dates'[Date], MIN('Dates'[Prior Quarter Date]), MAX('Dates'[Prior Quarter Date]))
)

NC Intenet Sales (PQ) =
VAR __MinPYDate = MIN('Dates'[Prior Quarter Date])
VAR __MaxPYDate = MAX('Dates'[Prior Quarter Date])
VAR __PQCalendar = CALENDAR(__MinPYDate, __MaxPYDate)
VAR __Table =
SUMMARIZE(
ALL('FactInternetSales'),
'FactInternetSales'[OrderDate],
"__Sales", SUM('FactInternetSales'[SalesAmount]))
VAR __Result = SUMX( FILTER( __Table, [OrderDate] IN __PQCalendar), [__Sales] )
RETURN
__Result
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@GuillaumePower I've pasted the formatted formula below. The entire code after the "," on line 5 is the final parameter for the SUMX function. Since VAR is used then it must have a matching RETURN statement.

``````Sales PQ =
IF (
[ShowValueForDates],
SUMX (
VALUES ( 'Date'[Fiscal Year Quarter Number] ),
VAR CurrentFiscalYearQuarterNumber = 'Date'[Fiscal Year Quarter Number]
VAR DaysSelected =
CALCULATETABLE (
VALUES ( 'Date'[Day of Fiscal Quarter Number] ),
REMOVEFILTERS (
'Date'[Working Day],
'Date'[Day of Week],
'Date'[Day of Week Number]
),
'Date'[DateWithSales] = TRUE
)
RETURN
CALCULATE (
[Sales Amount],
'Date'[Fiscal Year Quarter Number] = CurrentFiscalYearQuarterNumber - 1,
DaysSelected,
ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] )
)
)
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors