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

[WSeirafi]

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.

Thanks in advance for your help !

[tamerj1 · Accepted solution]

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 () )
)

[WSeirafi]

I think I owe you a beer !