Forum Discussion

FStettler's avatar
FStettler
Helper I
6 years ago

Comparing 2 dates not working

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.    
Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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-calculations/

     

    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's avatar
      FStettler
      Helper I
      6 years ago

      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's avatar
        parry2k
        Super User
        6 years ago

        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.⚡