Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
NickzNickz
Helper IV
Helper IV

Get most recent 12 quarter

Hi ... I have below measure to get last 3 year data.

Last 3 Year = 
VAR _currentyear = YEAR(TODAY())
VAR _previousyear = SELECTEDVALUE('Date Table'[Date].[Year])
RETURN
    SWITCH(
        TRUE(),
        _previousyear <= _currentyear -1 &&
        _previousyear >= _currentyear -3, 1,
        0
    )

 I also need a new measure to get the most recent 12 quarter for another chart ... How can I achieve that ... 

 

Tq in advanced.

 

Regards,

NickzNickz

4 REPLIES 4
123abc
Community Champion
Community Champion

plz try this measure:

 

Most Recent 12 Quarters =
VAR _currentQuarter = QUARTER(TODAY())
VAR _currentYear = YEAR(TODAY())
VAR _selectedQuarter = SELECTEDVALUE('Date Table'[Date].[Quarter])
VAR _selectedYear = SELECTEDVALUE('Date Table'[Date].[Year])
VAR _quartersAgo = (_currentYear - _selectedYear) * 4 + _currentQuarter - _selectedQuarter

RETURN
IF (
_quartersAgo >= 0 && _quartersAgo < 12,
1,
0
)

Hi @123abc ,

 

Not working... Blank data ... 😅

 

Regards,

NickzNickz

plz try this modified DAX:

 

Most Recent 12 Quarters =
VAR _currentQuarter = QUARTER(TODAY())
VAR _currentYear = YEAR(TODAY())
VAR _quartersToInclude = 12
VAR _selectedQuarter = SELECTEDVALUE('Date Table'[Date].[Quarter])
VAR _selectedYear = SELECTEDVALUE('Date Table'[Date].[Year])

RETURN
IF(
_selectedYear = _currentYear &&
_selectedQuarter >= _currentQuarter - _quartersToInclude,
1,
0
)

 

OR

 

I apologize for any confusion. Let's troubleshoot and refine the measure. Please ensure the following:

  1. Check Date Table Relationships:

    • Ensure that your Date Table is properly related to the fact table. There should be an active relationship between the date columns in the Date Table and the corresponding date column in the fact table.
  2. Check Column References:

    • Confirm that the column references in the measure match the actual column names in your Date Table. For example, replace 'Date Table'[Date].[Quarter] and 'Date Table'[Date].[Year] with the correct column names.
  3. Verify Data:

    • Make sure your Date Table contains data for the specified time range.

Here's the revised measure, including a fix for potential issues:

 

Most Recent 12 Quarters =
VAR _currentQuarter = QUARTER(TODAY())
VAR _currentYear = YEAR(TODAY())
VAR _quartersToInclude = 12
VAR _selectedQuarter = SELECTEDVALUE('Date Table'[Quarter]) -- Update with your actual column name
VAR _selectedYear = SELECTEDVALUE('Date Table'[Year]) -- Update with your actual column name

RETURN
IF(
NOT ISBLANK(_selectedQuarter) &&
NOT ISBLANK(_selectedYear) &&
_selectedYear = _currentYear &&
_selectedQuarter >= _currentQuarter - _quartersToInclude,
1,
0
)

 

Make sure to replace 'Date Table'[Quarter] and 'Date Table'[Year] with the actual column names from your Date Table. If the issue persists, there might be specific aspects of your data model or requirements that we need to consider.

Hi @123abc ,

 

I still got error... for info, my column has date, year and month...  Date/Month for 3, 6, 9 and 12 + 13 final value after audited.

 

Can we use Top N function ?  ... 

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors