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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jayanthan
Helper III
Helper III

Dynamic Last four quarters

Hi All

 

I would like get  X Axis as last four quarters based on the selected quarter and year in filters, example when i select Q4 in 2018, i want to show Q3,Q2,Q1 in 2018 and Q4 in 2017 in charts, if i select Q2 in 2018, i want to show Q1 in 2018 and Q4,Q3,Q2 in 2017 in charts

 

Please advise how can i acheive above mentioned results through functions or any other method

 

thank you 

 

 

Regards

Jayanthan

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @jayanthan

1.copy the "date" table to create a new table "date2"

don't connect this new table to other tables

add "year" and "quarter" from table "date2" to the slicer

9.png

8.png

 

2. create measures in the Sheet7

measure_value = SUM(Sheet7[value])

selected_Y = SELECTEDVALUE(Date2[Year])

selected_Q = SELECTEDVALUE(Date2[Quarter])

flag =
IF (
    MAX ( 'Date'[Year] ) = [selected_Y],
    IF ( MAX ( 'Date'[Quarter] ) < [selected_Q], 1 ),
    IF (
        MAX ( 'Date'[Year] ) < [selected_Y],
        IF ( MAX ( 'Date'[Quarter] ) >= [selected_Q], 1 )
    )
)

3. add [flag] in the Visual level filter

10.png

 

Best Regards

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @jayanthan

1.copy the "date" table to create a new table "date2"

don't connect this new table to other tables

add "year" and "quarter" from table "date2" to the slicer

9.png

8.png

 

2. create measures in the Sheet7

measure_value = SUM(Sheet7[value])

selected_Y = SELECTEDVALUE(Date2[Year])

selected_Q = SELECTEDVALUE(Date2[Quarter])

flag =
IF (
    MAX ( 'Date'[Year] ) = [selected_Y],
    IF ( MAX ( 'Date'[Quarter] ) < [selected_Q], 1 ),
    IF (
        MAX ( 'Date'[Year] ) < [selected_Y],
        IF ( MAX ( 'Date'[Quarter] ) >= [selected_Q], 1 )
    )
)

3. add [flag] in the Visual level filter

10.png

 

Best Regards

Maggie

Hi 

 

Its was working , great , thank you for the support

 

Regards

Jayanthn

Anonymous
Not applicable

Hi jayanthan,

Try this:

 

Measure = CALCULATE(SUM(Table1[Value]),DATESINPERIOD('date'[Date],MAX('date'[Date]),-4,QUARTER))

Or follow this link:

https://community.powerbi.com/t5/Desktop/Accumulated-doing-accumulated-12-months-for-every-month-of-...

 

Tell us if works for you.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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