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

Helper I

## Counting positive total over the last 6 months, SUMX not returning the expected result

Hello,

I've been having some trouble finding a DAX solution to my problem, let me expose it.

Before we start, I cannot share any data as its confidential.

The objective of this measure is to calculate over the fact table for eachy facility the quantity received over the last 6 months.

If the quantity is positive we flag 1, if not, -1, and then sum up all this.

For that purpose I created a last 6 month sum, then used a SUMX over a summarized table.

But since the fact table has months with no quantities received, the summarized table doesn't show data for those dates.

The result expected for the last column should be

1

1

1

3

Please I need help this is urgent.

1 ACCEPTED SOLUTION
Super User

=
VAR T1 =
VALUES ( DIM_CALENDAR[MONTH_YEAR] )
VAR T2 =
CALCULATETABLE ( VALUES ( FACT_SALES[Column1] ), ALL ( DIM_CALENDAR ) )
VAR T3 =
CROSSJOIN ( T1, T2 )
RETURN
SUMX (
T3,
"AVG",
VAR maxDate =
CALCULATE ( MAX ( DIM_CALENDAR[CALENDAR DATE] ) )
VAR stardate =
DATE ( YEAR ( maxDate ), MONTH ( maxDate ) - 6, DAY ( maxDate ) )
RETURN
CALCULATE (
SUM ( FACT_SALES[Column2] ),
DATESBETWEEN ( DIM_CALENDAR[CALENDAR_DATE], stardate, maxDate )
)
),
IF ( NOT ISBLANK ( [AVG] ), IF ( [AVG] >= 0, 1, -1 ), BLANK () )
)

2 REPLIES 2
Super User

=
VAR T1 =
VALUES ( DIM_CALENDAR[MONTH_YEAR] )
VAR T2 =
CALCULATETABLE ( VALUES ( FACT_SALES[Column1] ), ALL ( DIM_CALENDAR ) )
VAR T3 =
CROSSJOIN ( T1, T2 )
RETURN
SUMX (
T3,
"AVG",
VAR maxDate =
CALCULATE ( MAX ( DIM_CALENDAR[CALENDAR DATE] ) )
VAR stardate =
DATE ( YEAR ( maxDate ), MONTH ( maxDate ) - 6, DAY ( maxDate ) )
RETURN
CALCULATE (
SUM ( FACT_SALES[Column2] ),
DATESBETWEEN ( DIM_CALENDAR[CALENDAR_DATE], stardate, maxDate )
)
),
IF ( NOT ISBLANK ( [AVG] ), IF ( [AVG] >= 0, 1, -1 ), BLANK () )
)

Helper I

I think I owe you a beer !

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