Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Community Champion
Community Champion

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
Not applicable

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 @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 😉

Anonymous
Not applicable

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.