Comparing 2 dates not working

[FStettler]

Hi guys!

I'm stuck in a very (apparently) simple but deadend problem.

I have a table f-PICK UPS where transactions are recorded within, where recording date is one of the main fields I want to work with. Table goes:

Table f-PICK UPS

Column OCUPADOS (Integer)

Column FECHA TOMA (Date)

I have a Matrix where I want to sum the amount of OCUPADOS for the previous recording date with data, and then compare it to the current date of the same measure that sums OCUPADOS.

One important thing is that not every day in the calendar has data. Like one recording date is 17/02/2020 but the next one is 19/02/2020. This doesn't follow a pattern, it just depends on when the data is collected. So I cant use PREVDATE function.

I've managed to work out the previous date with data with using this measure

PREVDATE = CALCULATE ( MAX ( 'f-PICK UPS'[FECHA TOMA]) , FILTER ( ALL ( 'f-PICK UPS'[FECHA TOMA]) , 'f-PICK UPS'[FECHA TOMA] < MAX('f-PICK UPS'[FECHA TOMA]) ) )

This works just fine.

Then I used a DATE function to be 100% sure it's going to be a date I'll be working with

PREVDATE FORMAT = DATE ( YEAR ([PREVDATE]) , MONTH ([PREVDATE]) , DAY ([PREVDATE]) )

But when I use PREVDATE FORMAT within a measure to actually work out the sum OCUPADOS it just won't compare the dates. Here's my measure

OCC DIA ANTERIOR = CALCULATE ( [Q-OCUPADOS] , FILTER ( ALLSELECTED('f-PICK UPS') , 'f-PICK UPS'[FECHA TOMA] = [PREVDATE FORMAT]) )

Results will come up as blank.

Just to double-check, I'm replacing PREVDATE FORMAT with a hard-coded-date like DATE(2020,2,17) and it will sum OCUPADOS for that date in all rows in the matrix, which expectable. But when I use PREVDATE FORMAT it just won't run any results.

I've also pulled PREVDATE FORMAT into the matrix to see what it's actually doing, and it's returning the correct date (which is the previous date with data). 

I really don't know what's going on. If someone could lend me a hand with this I'll be much appreciated.

Here's the matrix

Thanks a mill!

Facundo.    

[parry2k]

@FStettler As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

Once you set a relationship between data and transaction table, you can simply add previous day measure as below

Previous Day = 
CALCULATE ( SUM ( Table[Amount] ), PREVIOUSDAY ( DateTable[Date] ) )

or

Previous Day = 
CALCULATE ( SUM ( Table[Amount] ), DATEADD ( DateTable[Date] ), -1, DAY )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[FStettler]

Hi @parry2k  thanks for your swift answer.

I've tried all that. I have my calendar table set up as date table, and the relationship made.

PREVIOUSDAY won't work as I don't need the immediate previous date like in a continuous calendar table. I need the previous date WITH DATA. In my example, some data was collected on 17/02/2020 and the next day where data was collected was 19/02/2020, and then the next one was 11/06/2020. This doesn't follow a pattern.

PREVIOUSDAY and DATEADD in your example are related to the immediate previous date, which is most likely to be blank in my model.

Thanks again.

Facundo

[parry2k]

@FStettler ok in that case try this

Prev Day = 
VAR __prevDate = CALCULATE ( MAX ( TransactionTable[Date] ), FILTER ( ALL ( DateTable[Date] ), DateTable[Date] < MAX ( DateTabble[Date] ) ) )

RETURN

CALCULATE ( SUM ( Table[Amount] ), DateTable[Date] = __prevDate )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[FStettler]

@parry2k thanks again for your swift reply.

I've modified everything taking dimension date table in. But I get this message when running then CALCULATE function

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

Thanks

[parry2k]

@FStettler I think we are over complicating it, send your pbix file with sample data, remove any sensitive information before sharing and I will send back the updated pbix file.

[parry2k]

@FStettler and one thing when you are visualizing your data, make sure you are taking date from date dimension table, not from the transaction table as that date will be driving the logic.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡