Show data for last 5 quarters from Fact Table

[Anonymous]

Hi All,

We need your help here. We are showing data quarterly based on the latest quarters available in Fact table. The latest quarter in Fact table is Apr-Jun 2019. We want our report should show last 5 quarters dynamically. which means 

Apr- Jun 2018

Jul - Sep 2018

Oct- Dec 2018

Jan - Mar 2019

Apr-Jun 2019

Please advise how can we achieve this.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

Hi Ashish @Ashish_Mathur ,

Thanks for your reply. We dont have any date Slicer on the report to make any user selection. There is just a Bar graph which shows data for last 5 quarters and currently the latest quarter available is Apr-June 2019.

Thanks

[Ashish_Mathur]

Hi,

I am not sure of how much i can help but can definitely try if you can share your PBI file.

[Anonymous]

Hi  Ashish @Ashish_Mathur ,

Thanks for helping me in this. Sorry, i am not allowed to share the actual data. But to describe my issue, We can go with an example. We store sale data quarterly, it does not get updated on daily basis. As per current date, the latest quarter we have is Apr-June 2019. 

And when we say Sales in last 5 quarters 

I hope it makes sense to you.

[Ashish_Mathur]

Hi,

Your question made sense to me the first time i read it.  However, i cannot help you if i do not have data to work with.

[jdbuchanan71]

Hello @Anonymous 

You can add a column to your date table using this code.

QTR Offset = DATEDIFF ( TODAY(), Dates[Date], QUARTER )

Then in your report add the [QTR Offset] to the filters and set it to

>= -4 AND <= 0

This will keep the current quarter and last 4 quarters selected in your report and will adjust automatically when you go from quarter to quater.

[Anonymous]

Hi @jdbuchanan71 

Thanks for your reply.

This solution works with Today's date, for which data is not available. Data gets updated quarterly.

The latest data available in fact table is for Apr-June 2019, which is not the current quarter.

Please advise.

Thanks

[jdbuchanan71]

Hello @Anonymous 

Set the [QTR Offset] filter to cover the period that always has the latest data.  If it is updated once a QTR but only through last QTR you would set it to >= -5 and <= -1