Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago
Solved

Quarter based on predefined date Range

I have a quarter referrence table for Quarter. In my main table (table 1) i have to get the quarter next to the date column. 

 

For example,

table 1 date is 20-3-2019 then quarter should get Qtr 1 2019.  Like this it to check all each row and give the quarter.

Start DateEnd DateQuarter
16-01-201915-04-2019Qtr 1 2019
16-04-201915-07-2019Qtr 2 2019
16-07-201915-10-2019Qtr 3 2019
16-10-201915-01-2020Qtr 4 2019
16-01-202030-04-2020Qtr 1 2020
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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 Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    unnijoy , if you can have a year start date then it would

     

    Qtr No = "Q"& (QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Start Date],MONTH),3)+1) & " " & year('Date'[Start Of Year])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

     

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      6 years ago

      Sorry. 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 DateEnd DateQuarter
      16-Jan-1915-Apr-19Qtr 1 2019
      16-Apr-1915-Jul-19Qtr 2 2019
      16-Jul-1915-Oct-19Qtr 3 2019
      16-Oct-1915-Jan-20Qtr 4 2019
      16-Jan-2030-Apr-20Qtr 1 2020

       

      Below is table 2

      DateQuarter
      18-01-2019Qtr 1 2019
      02-05-2019Qtr 2 2019
      28-07-2019Qtr 3 2019
      22-11-2019Qtr 4 2019
      28-04-2020Qtr 1 2020
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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