Current Quarter Year Table versus Previous Quarter Table

[acg]

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?

[Whitewater100]

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.

[amitchandak]

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

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