Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
WSeirafi
Helper I
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.

 

WSeirafi_0-1675961269389.png

 

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

 

WSeirafi_1-1675961357434.png

 

The result expected for the last column should be

1

1

1

3

 

Please I need help this is urgent.

 

Thanks in advance for your help !

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @WSeirafi 

please try

=
VAR T1 =
VALUES ( DIM_CALENDAR[MONTH_YEAR] )
VAR T2 =
CALCULATETABLE ( VALUES ( FACT_SALES[Column1] ), ALL ( DIM_CALENDAR ) )
VAR T3 =
CROSSJOIN ( T1, T2 )
RETURN
SUMX (
ADDCOLUMNS (
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 () )
)

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @WSeirafi 

please try

=
VAR T1 =
VALUES ( DIM_CALENDAR[MONTH_YEAR] )
VAR T2 =
CALCULATETABLE ( VALUES ( FACT_SALES[Column1] ), ALL ( DIM_CALENDAR ) )
VAR T3 =
CROSSJOIN ( T1, T2 )
RETURN
SUMX (
ADDCOLUMNS (
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 () )
)

I think I owe you a beer !

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors