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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TestTest17891
Advocate I
Advocate I

Count from the selection to the last 3 quarters backawards

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

1 ACCEPTED SOLUTION

11 REPLIES 11
gmsamborn
Super User
Super User

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)

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

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.

 

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

Hi @TestTest17891 

 

Can you try this?

 

Curr Q vs 3 Prev Qtrs - 3a.pbix

 

gmsamborn
Super User
Super User

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?

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

Wouldn't the last qtr listed be from 2022?

Yes sorry ! I corrected it

gmsamborn
Super User
Super User

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

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
thumbnail_image001.png

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

Same thing for this selection

thumbnail_image003.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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