Dynamically display past year, previous years all 4 quarters and year and present year quarters

[sridharbabuk]

i have requirement to show the title and also the calculation. first we need columns to dynamically display past year, previous year quarters and year and present year quarters and months if not completed quarters and so on.

for eg:

2023 Q1-24, Q2-24, Q3-24, Q4-24, 2024, Q1-25, Apr-25, May-25.

in this format has to show column headings has to place in matrix columns,

after jun 25, automatically has to show Q1-25, Q2-25.

[v-csrikanth]

Hi @sridharbabuk 
If you get any chance to provide the sample data so that will provide the guidance to resolve your issue.

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

Best Regrads,
Cheri Srikanth

[v-csrikanth]

Hi @sridharbabuk 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to share the pbix file and sample data. If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.

Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @sridharbabuk 
Thanks for reaching out to the Fabric Community.
Could you please provide the sample data and your requirement in detail so that we can assist you on your issue.

Best Regards,
Cheri Srikanth

[v-csrikanth · Accepted solution]

Hi @sridharbabuk 

Thank you for being part of the Microsoft Fabric Community.
Please do follow the below steps to resolve your issue.

1. Create a dynamic “Periods” table in Power BI using DAX and the TODAY() function, for example:
   Periods =
   VAR Today = TODAY()
   VAR CY = YEAR(Today)
   VAR CM = MONTH(Today)
   VAR PY = CY - 1
   VAR LastFullQ = ROUNDUP(CM/3,0) - 1
   VAR PrevYearQuarters =
   ADDCOLUMNS(
   GENERATESERIES(1,4,1),
   "Period", "Q" & [Value] & "-" & RIGHT(PY,2),
   "Sort", [Value]
   )
   VAR CurrYearQuarters =
   ADDCOLUMNS(
   GENERATESERIES(1, LastFullQ, 1),
   "Period", "Q" & [Value] & "-" & RIGHT(CY,2),
   "Sort", 100 + [Value]
   )
   VAR CurrYearMonths =
   ADDCOLUMNS(
   GENERATESERIES(LastFullQ*3 + 1, CM, 1),
   "Period", FORMAT(DATE(CY,[Value],1), "MMM-yy"),
   "Sort", 200 + [Value]
   )
   VAR Result =
   UNION(
   ROW("Period", FORMAT(PY,"YYYY"), "Sort", 0),
   PrevYearQuarters,
   ROW("Period", FORMAT(CY,"YYYY"), "Sort", 50),
   CurrYearQuarters,
   CurrYearMonths
   )
   RETURN
   SELECTCOLUMNS(Result, "PeriodLabel", [Period], "SortOrder", [Sort]
   
   
2. Set PeriodLabel as your matrix’s column field and sort it by SortOrder.
3. Create a measure that picks the correct value for each PeriodLabel (e.g., by matching Fact[PeriodKey] = SELECTEDVALUE(Periods[PeriodLabel])).
4. The matrix will then automatically show:

• Past Year (e.g. 2023)
• Previous Year Quarters (Q1-23…Q4-23)
• Current Year (2024)
• Completed Quarters (Q1-24…Qn-24)
• Ongoing Months (e.g. Apr-24, May-24)

Once June rolls in (CM = 6), LastFullQ becomes 2 and the table updates to show Q1-24, Q2-24 instead of April/May.

This approach requires no manual updates—just refresh your model each month, and the Periods table drives both the column headers and the associated calculations.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[sridharbabuk]

i am using the below dax as calculated column in date table.

DynamicPeriodFuture =

VAR _Today = TODAY()

VAR _Date = 'DateTable'[Date]

VAR _Year = YEAR(_Date)

VAR _Month = MONTH(_Date)

VAR _Quarter = QUARTER(_Date)

VAR _CurrentYear = YEAR(_Today)

VAR _CurrentMonth = MONTH(_Today)

VAR _CurrentQuarter = QUARTER(_Today)

VAR _IsPastQuarter =

    _Year < _CurrentYear || (_Year = _CurrentYear && _Quarter < _CurrentQuarter)

VAR _IsCurrentQuarterMonth =

    _Year = _CurrentYear && _Quarter = _CurrentQuarter && _Month <= _CurrentMonth

VAR _IsFutureQuarter =

    _Year > _CurrentYear || (_Year = _CurrentYear && _Quarter > _CurrentQuarter)

VAR _QuarterLabel = "Q" & _Quarter & "-" & RIGHT(_Year, 2)

VAR _MonthLabel = FORMAT(_Date, "mmm") & "-" & RIGHT(_Year, 2)

VAR _YearLabel = FORMAT(_Date, "yyyy")

RETURN

SWITCH(TRUE(),

    -- Past years

    _Year < _CurrentYear, _YearLabel,

    -- Quarters of 2024 and 2025

    _Year IN {2024, 2025} && _IsPastQuarter, _QuarterLabel,

    _Year IN {2024, 2025} && _IsCurrentQuarterMonth, _MonthLabel,

    _Year IN {2024, 2025} && _IsFutureQuarter, _QuarterLabel,

    _Year IN {2024, 2025} && _Month = 12 && _Today > DATE(_Year, 12, 31), _YearLabel,

    -- Other future years

    _IsPastQuarter, _QuarterLabel,

    _IsCurrentQuarterMonth, _MonthLabel,

    _IsFutureQuarter, _QuarterLabel,

    -- Completed years

    _Year = _CurrentYear && _Month = 12 && _Today > DATE(_Year, 12, 31), _YearLabel,

    BLANK()

)but it showing 2023,Q1-25,APR-25, MISSING Q1-24 to Q4-24 And 2024. suggest me on this

[freginier]

To achieve dynamic columns in a Power BI matrix (e.g., "2023 Q1", "Apr-25"), create a calculated column in your Date table (or a separate table) that generates these specific period labels based on TODAY() (or a selected date). You'll also need a corresponding sort column for these labels to ensure correct chronological order in the matrix. Place this "Period Label" column in the Matrix's "Columns" well and your measures in the "Values" well. The exact logic for generating the labels (e.g., distinguishing between full years, quarters, and months) will depend on your specific display rules.