Measure with calculate before date not working

[bo_afk]

I've created a measure to calculate another measure only for dates before a particular date.

i.e. CALCULATE( [measure] , [date] < 01/09/2019)

But my result is coming up as blank. Can someone tell me what is wrong with this formula? Reason for this formula is that I want to create a measure for before and after this date and then calculate the difference between these.

[v-eachen-msft · Accepted solution]

Hi @bo_afk ,

CALCULATE needs two kinds of parameter: Expression and Filter. So you need a table range to get a filter.

So you could try the following DAX:

Measure =
CALCULATE ( [measure], FILTER ( 'Table', [date] < DATE ( 2019, 1, 9 ) ) )

[bo_afk]

Thanks @v-eachen-msft. This looks to have done the trick!

[Anonymous]

Hi @bo_afk ,

I'm not totally sure because dates are always something tricky 😁.

Try to format it inside DATE function I think that would pass it with no problems.

Let me know if it helped.

BR,

DR

[bo_afk]

Thanks for your suggestion @Anonymous 

I did try using the date function but it didn't seem to work. I suspect that's because it outputs a datetime format and my data has only date. I think tried to format it as "dd/mm/yyyy" but then got an error that I can't compare date values with text.

Update: so the date function works when I use a simple sum as my calculation, but when I input my measure, it doesn't work. Must be an issue with my measure then.

[Anonymous]

Most likely, I would need some sample data to make a few tests but that usually work.

[bo_afk]

In general, does the calculate formula work with measures and dates?