DAX - compare dates against unrelated date table?

[Anonymous]

Hi, hopefully someone can help with this.

I have an SSAS tabular model and I am trying to add a dynamic calculated column which will set to 1 if the ClosedDate > Start of selected month, and the CreatedDate < start of selected month.

I have the following DAX however this isn't setting the flag for some reason? Does anyone know what I am doing wrong?

=
VAR __mindate =
STARTOFMONTH('Date'[FullDate])
RETURN

IF(Query[DateClosed]>= __mindate && Query[DateAdded] < __mindate,1,0)

So for the attached example, VAR __mindate should be set to '01-jul-2021' (as this month is what is selected in my slicer) and then flag should be set to 1 on the IDs 3971, 3964 and 3967 as these 3 rows meet the condition. It is currently setting them all to 0.

[Anonymous]

For my own understanding & learning, would this have even been achievable as a calculated column?

[nvprasad · Accepted solution]

Hi JD09631,

If you are trying to create a measure then you need to wrap it with selectedvalue.

Could you please try with below dax?

VAR __mindate =
STARTOFMONTH('Date'[FullDate])
RETURN

IF(selectedvaue(Query[DateClosed])>= __mindate && selectevaue(Query[DateAdded] )< __mindate,1,0)

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[Anonymous]

Nevermind I think it works fine, I didn't need to use calculated column afterall this works fine as a measure 🙂

[Anonymous]

Hi, I am trying to add it as a calculated column. The above isn't working for me still, unfortunately. Still 0 for all rows 😞