Get most recent 12 quarter

[NickzNickz]

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

[123abc]

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
)

[NickzNickz]

Hi @123abc ,

Not working... Blank data ... 😅

Regards,

NickzNickz

[123abc]

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.

[NickzNickz]

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 ?  ...