Hello,
I have a DAX measure to calculate new customers, and it does not work correctly. I'm trying to base it on an Excel calculation IF(AND(Dec'22=0,Sep'23>0), Sep'23, 0). Not only that, but I want to modify it a bit to also include any date before Dec'22, and the Sep'22 would need to be any month in 2023.
This is my measure:
test 3 =
VAR __salesuptodec22 =
CALCULATE(
SUM('Financial report'[Salespmth])
,DATESBETWEEN(
'Financial report'[Cal_Date],
MIN('Financial report'[Cal_Date]),
DATE(2022,12,31)
)
)
VAR __dec22 =
IF(__salesuptodec22=0,1)
VAR __jan23ow =
IF(
CALCULATE(
SUM('Financial report'[Salespmth]),
DATESBETWEEN(
'Financial report'[Cal_Date],
DATE(2023,01,01),
MAX('Financial report'[Cal_Date])
)
)
>0,1
)
VAR __nc =
IF(__dec22 =1 && __jan23ow = 1,"new")
RETURN
IF(__nc="new",SUM('Financial report'[Salespmth]))
When I put it in a matrix, with company name in rows, Date in columns and test 3 in values, it still returns everything.
Would someone be able to help me fix the measure so it only would return new customers?
Thank you for your help and time.
