topic Counting positive total over the last 6 months, SUMX not returning the expected result in DAX Commands and Tips

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

WSeirafi — Thu, 09 Feb 2023 16:50:26 GMT

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

Please I need help this is urgent.

Thanks in advance for your help !

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

tamerj1 — Thu, 09 Feb 2023 17:35:11 GMT

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

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

WSeirafi — Fri, 10 Feb 2023 14:52:24 GMT

I think I owe you a beer !