Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Comparing a date to date range

Hi

I've a requirement where I need to compare transaction date with date ranges to figure out the period that I want to assign to this date.

So, the data I have is:

Transaction DateFREQ
12-Jun-202
13-Jun-203
14-Jun-204
22-Jun-208
23-Jun-2012
21-Jul-204
20-Oct-206
18-Nov-2016
18-Nov-2010

 

I wish to add a calculated column (PERIOD) to the above data so that it shows in the following way:

Transaction DateFREQPERIOD
12-Jun-202P3
13-Jun-203P3
14-Jun-204P3
22-Jun-208P4
23-Jun-2012P4
21-Jul-204P5
20-Oct-206P8
18-Nov-2016P9
18-Nov-2010P9

 

To know the period, I use the table below: 

Date RangePeriod
01 APR - 25 APRP1
26 APR - 23 MAYP2
24 MAY - 20 JUNP3
21 JUN - 18 JULP4
19 JUL- 15 AUGP5
16 AUG - 12 SEPP6
13 SEP - 10 OCTP7
11 OCT - 07 NOVP8
08 NOV - 05 DECP9
06 DEC - 02 JANP10
03 JAN - 30 JANP11
31 JAN - 27 FEBP12
28 FEB - 31 MAR

P13

 

I'm just stuck and can't figure out a way to compare dates so I can use something like SWITCH function.

Need Help

5 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    You would probably be better off adding the field to determine Period to your date table.  If you did that, you'd be able to just lookup the value of the date in question in your date table and return the Period column.

     

    Hope that helps!  ğŸ™‚

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi

      I don't think I understand what you are saying. But my challenge is on how to do the date comparison. How can I say (if the transaction date between 1 April  and April 25, then it's P1 ...etc)

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        5 years ago

        Hi Anonymous 

        It's almost a prerequisite that a data model have a date table and that it's marked as one.  A date table is basically a continuous list of dates from the beginning of time (according to your data model) to the end of time (again, according to your data model).  It is occasionally useful to include in the date table fields such as MonthName, DayOfWeekName, etc.  It seems that in your case having a field of "Period" (and I would suggest Period and Year) would be very useful.

         

        If you took the logic out of creating measures and built it into the data model your problem would be easily solved.  The calculated column to determine this would come down to (and I'm giving this freehand in Notepad with little knowledge of your model)

         

        LOOKUPVALUE(
	DateTable[YearPeriod],
	DateTable[Date],
	SourceTable[TransactionDate]
)

         

        It could really be that easy.

        Let me know how this works ğŸ˜‰