Count from the selection to the last 3 quarters backawards

[TestTest17891]

I have 2 tables in Oracle that I use in SSAS Tabular model :

MyFactTable
DimDate

The relationship beteween them is 1 to many
MyFactTable[FileExtractDate] to DimDate[Date]

A simple count of the RAF ID is
_count_Client_Distinct = COUNT(MyFactTable[Client ID])

now I want to calculate based on the selection the count of the RAF ID
and the last 3 quarters

For example:

if I select FileExtractDate= 12/10/2021
the visual of chart bars will give me the count of the Client ID of the Q4 since 12/10/2021 belongs to the 4th quarter
and displays the count of Client ID in the 3 other quarters (Q1, Q2, Q3)

if I select FileExtractDate= 09/02/2021
the visual of chart bars will give me the count of the Client ID of the Q1 since 09/10/2021 belongs to the 1st quarter
and displays the count of Client ID in the 3 other quarters (Q2, Q3, Q4) but for 2020

without any selection it will take the max date and show the count for the quarter of the max date and the last 3 quarters

[gmsamborn]

Which order would you like?

In the 1st example, the order is ascending (2023-Q1, 2023-Q2, 2023-Q3, 2023-Q4).

In the 2nd example, the order is descending (2023-Q1, 2022-Q4, 2022-Q3, 2022-Q2).

Do you always want the 3 preceding Qtrs to be at the top before the current Qtr at the bottom?  (Ascending)

[TestTest17891]

I want always the order to be ascending like you mentioned here 

In the 1st example, the order is ascending (2023-Q1, 2023-Q2, 2023-Q3, 2023-Q4).

[gmsamborn]

Hi @TestTest17891 

Would the following help?

- I loaded some random data into ‘MyFactTable’.
- I created a date table with [Yr-Qtr] and [Yr-Qtr number] columns
- I created a disconnected slicer table ‘Qtrs’ by summarizing the above 2 columns.
- I created a 1:* (single) relationship between ‘Date’[Date] and ‘MyFactTable’[Date].

- I sorted [Yr-Qtr] by [Yr-Qtr number] in both 'Date' and 'Qtrs'.

- I created a measure like below.  I started out looking at OFFSET() but didn't have any luck so I ended up doing it the hard way.

Count of Clients - 2 = 
VAR _SelectedQuarter = SELECTEDVALUE( 'Qtrs'[Yr-Qtr] )
VAR _Prev1Quarter =
	SWITCH(
		RIGHT( _SelectedQuarter, 1 ),
		"1", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q4",
		"2", LEFT( _SelectedQuarter, 6 ) & "1",
		"3", LEFT( _SelectedQuarter, 6 ) & "2",
		"4", LEFT( _SelectedQuarter, 6 ) & "3"
	)
VAR _Prev2Quarter =
	SWITCH(
		RIGHT( _SelectedQuarter, 1 ),
		"1", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q3",
		"2", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q4",
		"3", LEFT( _SelectedQuarter, 6 ) & "1",
		"4", LEFT( _SelectedQuarter, 6 ) & "2"
	)
VAR _Prev3Quarter =
	SWITCH(
		RIGHT( _SelectedQuarter, 1 ),
		"1", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q2",
		"2", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q3",
		"3", ( VALUE( LEFT( _SelectedQuarter, 4 ) ) - 1 ) & "-Q4",
		"4", LEFT( _SelectedQuarter, 6 ) & "1"
	)
VAR _Result =
    CALCULATE( 
        COUNT( 'MyFactTable'[Client ID] ),
        'Date'[Yr-Qtr] IN { _SelectedQuarter, _Prev1Quarter, _Prev2Quarter, _Prev3Quarter }
    )
RETURN
    _Result

Curr Q vs 3 Prev Qtrs - 2.pbix

Let me know if you have any questions.  (It seems to work for me.)

PS: I'm pretty sure it could be done with WINDOW instead of OFFSET like I originally thought.

[TestTest17891]

Hello I appreciate your help, but in my slicer does not contain the Quarter, it contains the date.

And the format of the Quarter Year is Quarter Year for example Q1 2023

[gmsamborn · Accepted solution]

Hi @TestTest17891 

Can you try this?

Curr Q vs 3 Prev Qtrs - 3a.pbix

[gmsamborn]

Hi @TestTest17891 

Sorry.  I thought you meant a total of the 3 preceding qtrs. 

So, you want to see the current qtr and the 3 preceding qtrs.  ( ie. a whole year )

Is this correct?

[TestTest17891]

Let me explain better : 

for example if i select '01/12/23' and '01/12/23' belongs to the quarter 3 of 2023 so the result will be :

quarter-year count

q1 2023       100

q2 2023       50

q3 2023       86

q4               269

When i select '01/01/23' and '01/01/23' belongs to the quarter 1 of 2023 it will show data for :

Q1 2023

Q4 2022

Q3 2022

Q2 2022

[gmsamborn]

Wouldn't the last qtr listed be from 2022?

[TestTest17891]

Yes sorry ! I corrected it

[gmsamborn]

Hi @TestTest17891 

Would a measure like this help?

Prev 3 Qtrs = 
VAR _End = STARTOFQUARTER( 'Date'[Date] ) - 1
VAR _Start =
    CALCULATE(
        MIN( 'Date'[Date] ),
        DATEADD(
            DATEADD(
                'Date'[Date],
                -1,
                YEAR
            ),
            1,
            QUARTER
        )
    )
VAR _Result =
    CALCULATE(
        [Count],
        'Date'[Date] >= _Start
            && 'Date'[Date] <= _End
    )
RETURN
    _Result

Curr Q vs 3 Prev Qtrs.pbix

(I also calculated for distinct Client IDs before I reread your post.)

[TestTest17891]

I tried the measure but when I don't select anything in the period selection :
I don't get the Q2 the count of employeeID belonging to the period selection = 01/06/2023

Same for when I select 01.12.2023
I don't get the quarters before

Same thing for this selection