Forum Discussion

acg's avatar
acg
Icon for Resolver I rankResolver I
4 years ago

Current Quarter Year Table versus Previous Quarter Table

For a date calculation, which is driven by two independent date tables, I need a dynamic Current Quarter-Year Table and a corresponding (dynamic) Previous Quarter-Year Table. 

The Current Quarter-Year Table is simply:

 

Current Quarter-Year = DISTINCT(dim_date[year_quarter])
 
The dim_date table just simply has all sort of date formats, and I have to use the year_quarter format.
It is important to note that in this case year_quarter is in text format. 
 
How do I get a corresponding table  with the Previous Quarter-Year? 
So Current would be for example: Q3 2020 and the corresponding Previous Quarter-Year would then need to be Q2 2020. 
I tried this: Previous Quarter-Year= DISTINCT((dim_date[year_quarter])-1) 
resulting in this mistake :
"A single value for column 'year_quarter' in table 'dim_date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
 
Fair enough, the mistake makes sense. How can I refer in Previous Quarter-Year to the Current Quarter-Year table and then say, take the previous Quarter of the Current table?
 

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    acg ,

    Hope you need flag only

    Switch(True() ,

    [year_quarter] = Format(today(), "\QQ YYYY") , "This QTR",

    [year_quarter] = Format(eomonth(today(),-3), "\QQ YYYY") , "Last QTR",

    [year_quarter] )

    • acg's avatar
      acg
      Icon for Resolver I rankResolver I
      4 years ago

      I really need a table in order to make the 30 odd other measues in the dashboard working that use that above table. 

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    You can add these calculated columns into your table(s):

    IsCurrentQtr =
    IF (
    YEAR ( data[Date] ) = YEAR ( TODAY () )
    && QUARTER ( data[Date] ) = QUARTER ( TODAY () ),
    "Yes",
    "No"
    )
     
    IsCurrent/prev/Qtr =
    IF (OR(
    YEAR ( data[Date] ) = YEAR ( TODAY () )
    && QUARTER ( data[Date] ) = QUARTER ( TODAY () ), YEAR ( data[Date] ) = YEAR ( TODAY ()-365 )
    && QUARTER ( data[Date] ) = QUARTER ( TODAY () -365)),
    "Yes",
    "No"
    )
     
    You can change the YES/No answer to 1 and 0 if you would like too.