How to Hide Last 4 Quarters data in X axis Dynamically

[rajendraongole1]

Hi

I have an X axis with continous quarterly data from 2017 till data. i want to hide last 4 quarters and show upto 2018-Q4.

if next april comes i.e 2020 -Q1 , i have to get 2019-Q1 automatically.

Eg: Xaxis :

2017-Q1,2017-Q2,2017-Q3,2017-Q4, 2018-Q1,2018-Q2,2018-Q3,2018-Q4, 2020-Q1 ( Here 2019 quarter data is on hide) now.

like wise  in april 2020 -Q1 comes automatically i have to show 2019-Q1 data

[v-shex-msft]

Hi @rajendraongole1,

Maybe you can add a measure to extract and compare current date and return tag, then use it as a filter on visual level to filter the last 4 quarters.

Applying a measure filter in Power BI 

If you are confused coding formula,  please share some dummy data to test.
Regards, 

Xiaoxin Sheng

[rajendraongole1]

Hi @v-shex-msft Xiaoxin Sheng,

YearQuarter	Value
2015-1	3384
2015-2	3334
2015-3	2000
2015-4	3218
2016-1	3392
2016-2	4327
2016-3	4141
2016-4	2611
2017-1	2530
2017-2	6835
2017-3	5888
2017-4	2351
2018-1	3567
2018-2	4499
2018-3	3742
2018-4	2253
2019-1	1232
2019-2	8654
2019-3	3455
2019-4	2233

Thanks for the reply, I have shared the attached Excel. with current Output & Expecting output.

I have data available for last year Quarter data, but i ha ve to hide last complete year data and have to show upto before completed Quarter information. once this current Quarter next month data comes  then only i have show 2019-Q1 .

I hope this information is helpful to understand.

Thanks 

Expected:

Next Quarter i.e., 2020-01 comes then only i have to report 2019-1 Quarter data (values should reflect automatically).

Thank you.

[v-shex-msft · Accepted solution]

HI @rajendraongole1 ,

You can try to use the following measure to check current category and return tag, then you can use it on the line chart visual level filter to only display 'Y' tag records:

Measure = 
VAR _last =
    MAXX ( ALLSELECTED ( Test[YearQuarter] ), [YearQuarter] )
VAR _curr =
    LEFT ( MAX ( Test[YearQuarter] ), 4 ) & RIGHT ( MAX ( Test[YearQuarter] ), 1 )
VAR prev =
    LEFT ( _last, 4 ) - 1
        & RIGHT ( _last, 1 )
RETURN
    IF ( _curr < prev, "Y", "N" )

Regards,

Xiaoxin Sheng

[rajendraongole1]

@ Team- Thank you for time and support

[CheenuSing]

Hi @rajendraongole1 ,

I loaded your data and tried to simulate the expected output.  

The solution assumes that you will be showing all the previous quarter compared with the max value of the quater year in the data. 

So this will not work based on any filter on date, if you have.

Paste the code in the powerquery

let
    Source = Excel.Workbook(File.Contents("C:\PowerBICommunity\QuarterData.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year Quarter", type text}, {"Amount", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Year Quarter", "Year Quarter - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Year Quarter - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Year Quarter - Copy.1", "Year Quarter - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year Quarter - Copy.1", Int64.Type}, {"Year Quarter - Copy.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Year Quarter - Copy.1", "Base Year"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year Quarter - Copy.2",  Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Year Quarter - Copy.2", "Base QUarter"}}),
    GetMax = List.Max(#"Renamed Columns1"[Year Quarter]),
    LoadMax = Table.AddColumn(#"Renamed Columns1","max",each GetMax),
    #"Split Column by Delimiter1" = Table.SplitColumn(LoadMax, "max", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"max.1", "max.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"max.1", Int64.Type}, {"max.2", Int64.Type}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"max.1", "MaxYear"}, {"max.2", "Max Quarter"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns2", "Custom", each if [Base Year] >= [MaxYear] then 0 else if [Base QUarter] <= [Max Quarter] then 1 else 0)
in
    #"Added Conditional Column"

Change the Souce line pointing to your data source and file name. 

What this does is, after loading the base data it finds the maximum year quarter combination and populates the same in a new column.

The original YearQuarter and the new column Max YearQuarter are split into year and quarter.

Then add a column comparing the OriginalYear with MaxYear and OriginalQuarter with MaxQuarter and set as 1 or 0.

The logic applied is if the OriginalYear is Equal to the MaxYear then set it as 0.- - to indicate not to show.

If the OriginalYear is less than the MaxYear then if the OrginalQaurter is less than equal to the MaxQuarter set as 1 -  to show.

Otherwise 0 - not to show.

The visual as per orginal data which has 2019-4 as the last YearQuarter

With base data upto 2019-4After adding 2020-1 to data

The chart 1 is with the data provided by show and does not show 2019 quaerters

The chart2 is with adding 2020-1 data.  Shows only quarter-1 data for all previous years.

Is this the expectation.

Try it out and let me know.

Cheers

CheenuSing

[CheenuSing]

Hi @rajendraongole1 ,

Can you share the pbix / data on Google /  One Drive and paste the linke here. Please also share what is the solution you expect.

Cheers

CheenuSing

[rajendraongole1]

Thanks for your reply.

I will two visuals :

Visual-1, as of now i have all Quarters data information till date.

Requirement is in Visual-2

though i have 2019 all Quarter data, I want to showcase data upto 2018-4 Quarter only untill 2020-01 the data of 2019.01 should comes automatically without using manual filter condition.

[rajendraongole1]

@CheenuSing  & @amitchandak  & @Greg_Deckler 

Any suggestions?

[tex628]

This dynamically identifies the 4 most recent quarters, including the current one:

Check = 
VAR CurrentQ = QUARTER(today())
VAR CurrentY = YEAR(Today())
Return
IF( YEAR('Table (2)'[Date]) = CurrentY ; 1 ;
IF( YEAR('Table (2)'[Date]) = CurrentY -1 && QUARTER('Table (2)'[Date]) >= 1+CurrentQ ; 1 ; BLANK()))

Br,
J

[Greg_Deckler]

Sample data would help.Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That said, do you have anything else than your Year Quarter to do date calculations on? If not, I would suggest creating a table via Enter Data that lists your year quarters. In the second column enter the last quarter that you actually want. Then in your measure, you can use LOOKUPVALUE to lookup the correct year quarter maximum to display and use that as a filter in your measure to return blank if you are beyond that year quarter.