cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayanthan Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Last four quarters

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
Anonymous
Not applicable

Re: Dynamic Last four quarters

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.

 

 

Community Support Team
Community Support Team

Re: Dynamic Last four quarters

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

Highlighted
jayanthan Member
Member

Re: Dynamic Last four quarters

Hi 

 

Its was working , great , thank you for the support

 

Regards

Jayanthn

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors