DAX relevent date

[MarkCBB]

Hi there,

I created the below Measure, for use in a KPI visual, the problem is the TODAY() part, the today needs to be relevent to the point of time.

KPI Phantom Stock = DIVIDE(
		CALCULATE(COUNTROWS(SALES),DATEDIFF(SALES[LAST RECPT Y/M],TODAY(),MONTH)>=6,SALES[DATE LAST SOLD]="ALERT",SALES[SOH]>0,SALES[LAST RECPT Y/M] >1899-12-30),
					CALCULATE(COUNTROWS(SALES),SALES[LAST RECPT Y/M] >1899-12-30),
					0)

My Sales table has a column for date called "DATE" (I also have a calender table). 

This measure should show me the times that have a DATE LAST SOLD of "ALERT" and the SOH must be greater than 0 and the LAST RECPT Y/M must be >= to the relevent date in teh sales table. 

Hope the above makes sence?

[v-qiuyu-msft]

Hi @MarkCBB,

It would be better if you could provide some sample data and clarify the expected results. So we can understand it better and try to work out the DAX.

Best Regards,
Qiuyun Yu

[EAfang]

Your explanation is not all that clear. Would be better if you provide a sample data uploaded in OneDrive or Google Drive so one can write the measure for you accurately. But some thoughts;

1. You do not really need the TODAY() function to get the most recent [non-zero] value. If your Calendar table is dates starts and ends same day as the Sale Table dates, then filter directly from Calendar wrapped in an IF([measure] <> BLANK()). Else filter Sales table dates directly with say VALUES(Sales[Date]).

2. Easier to write a smaller measure like 

[Total Sales] = COUNTROWS(SALES)

then reuse it on other measures.

Cheers

[Greg_Deckler]

If I understand what you are going for, you could use a variable to get the relevant date and then use it in your formula. I am not entirely clear on where to plug this in because you mention the TODAY() part but it seems like from your description you want to replace the 1899-12-30 part so I'm a bit confused. In any case, you could do something like:

KPI Phantom Stock = VAR mydate = CALCULATE(MAX(SALES[DATE LAST SOLD]),SALES[DATE LAST SOLD]="ALERT",SALES[SOH]>0)

RETURN CALCULATE(COUNTROWS(SALES),DATEDIFF(SALES[LAST RECPT Y/M],mydate,MONTH)>=6)

Something like that, again, not entirely clear on what you are going for here.