Comparing a date to date range

[YJAMOUS]

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 Date	FREQ
12-Jun-20	2
13-Jun-20	3
14-Jun-20	4
22-Jun-20	8
23-Jun-20	12
21-Jul-20	4
20-Oct-20	6
18-Nov-20	16
18-Nov-20	10

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

Transaction Date	FREQ	PERIOD
12-Jun-20	2	P3
13-Jun-20	3	P3
14-Jun-20	4	P3
22-Jun-20	8	P4
23-Jun-20	12	P4
21-Jul-20	4	P5
20-Oct-20	6	P8
18-Nov-20	16	P9
18-Nov-20	10	P9

To know the period, I use the table below: 

Date Range	Period
01 APR - 25 APR	P1
26 APR - 23 MAY	P2
24 MAY - 20 JUN	P3
21 JUN - 18 JUL	P4
19 JUL- 15 AUG	P5
16 AUG - 12 SEP	P6
13 SEP - 10 OCT	P7
11 OCT - 07 NOV	P8
08 NOV - 05 DEC	P9
06 DEC - 02 JAN	P10
03 JAN - 30 JAN	P11
31 JAN - 27 FEB	P12
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.

[littlemojopuppy]

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!  🙂

[YJAMOUS]

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]

Hi @YJAMOUS 

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 😉

[YJAMOUS]

I agree with what you said in terms of create Date Table, and I did this. Again, my challenge is to define the period based on the table below:

So, below is the screenshot of the date Table I have and still I can't figure what DAX function to put in order to calcuate the period column.

[littlemojopuppy]

@YJAMOUS you would hard code this into the table, just like date, year, etc.