cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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 3
Solution Sage

Hi:

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.
Super User

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

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors