Forum Discussion
Post ProdigyQuarter based on predefined date Range
unnijoy ,
See if this work.
1. Table 1
2. Table 2
3. Create a Calculated Column
StartDatefromTable1 = CALCULATE (SELECTEDVALUE ( Table1[Start Date], 0 ),FILTER (ALLNOBLANKROW ( Table1[Start Date] ),Table1[Start Date] < Table2[Date]),FILTER (ALLNOBLANKROW ( Table1[End Date]),Table1[End Date] > Table2[Date]),REMOVEFILTERS ( ))4. Link the relationship of Table1 and Table 2 between Columns StartDatefromTable1 (Table2) and StartDate (Table1)5. Create a calculated Column.QuarterfromTable1 = RELATED(Table1[Quarter])Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Hi unnijoy ,
Check the formula below and see if the result is what you want.
Measure = IF(SELECTEDVALUE('Table 1'[date])>=SELECTEDVALUE(reference[Start Date])&&SELECTEDVALUE('Table 1'[date])<=SELECTEDVALUE(reference[End Date]),1,BLANK())
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Post ProdigySorry. I think my explanation was not addiquate.
I have 2 table. Table one is referrence table. And Table 2 is working table.
Table 2 have a date column. I need to add a Quarter column to this table (Table 2). The condetion is that it shlould check the date in Table 2 and from Table 1 i should find under which quarter that data from Table 2 falls under and that quarter sholuld reflect in table 2 Quarter column. Its a kind of vlookup. I try to use lookup. but as the date are repeating it is not giving me a correct output.
Table 1 is shown below.
| Start Date | End Date | Quarter |
| 16-Jan-19 | 15-Apr-19 | Qtr 1 2019 |
| 16-Apr-19 | 15-Jul-19 | Qtr 2 2019 |
| 16-Jul-19 | 15-Oct-19 | Qtr 3 2019 |
| 16-Oct-19 | 15-Jan-20 | Qtr 4 2019 |
| 16-Jan-20 | 30-Apr-20 | Qtr 1 2020 |
Below is table 2
| Date | Quarter |
| 18-01-2019 | Qtr 1 2019 |
| 02-05-2019 | Qtr 2 2019 |
| 28-07-2019 | Qtr 3 2019 |
| 22-11-2019 | Qtr 4 2019 |
| 28-04-2020 | Qtr 1 2020 |
Hey,
Why do you need to do a Lookup.
Just create a column =
Quarter = FORMAT(Table6[Date],"\Qtr q") & " " & year(Table6[Date])No need to do a LOOK UP.
Regards,
Harsh Nathani
- unnijoy
Below are the two table. In Data Table i need to include a DAX that will give me Quarter. Quarter should be based on Referrence table.
Referrence Table
Start Date End Date Quarter 16-Jan-19 15-Apr-19 Qtr 1 2019 16-Apr-19 15-Jul-19 Qtr 2 2019 16-Jul-19 15-Oct-19 Qtr 3 2019 16-Oct-19 15-Jan-20 Qtr 4 2019 16-Jan-20 30-Apr-20 Qtr 1 Data Table
Month Quarter 18-01-2019 Qtr 1 2019 12-06-2019 Qtr 2 2019 10-10-2019 Qtr 3 2019 12-06-2019 Qtr 2 2019 18-01-2019 Qtr 1 2019 18-01-2020 Qtr 1 2020 29-04-2020 Qtr 1 2020 - amitchandak
Super User
