cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YJAMOUS
Helper III
Helper III

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.

5 REPLIES 5
littlemojopuppy
Super User
Super User

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

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)

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 😉

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:

YJAMOUS_1-1626922368604.png

 

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.

 

YJAMOUS_0-1626922271097.png

 

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors