Dynamically display last 8 quarters

[calgary_raptor]

Hi everyone,

I have a question regarding how to correctly and dynamically display 8 quarters (current quarter and last 7 quarters) for a report. Before I go too far in my explanation, the function TODAY() will never work correctly because it looks at today's date and does not take into considertation all of my requirements. I should add that I am connecting to a SharePoint on prem folder directly to acquire my data. Here is what I think would work best but I don't think Power BI can do this:

1. Best case scenario would be to have a table that would contain just the records that need to be seen for the current quarter and last 7 quarters (not always feasible though). What I mean by this is to create a subset of the data you need to see from your main table that houses all the data from the current quarter and the beginning the last quarter. Recognizing that your existing data table would very likely have much more than just 8 quarters worth of data. Could have multiple years or even decades worth of data.
2. The table would need to dynamically drop the old records as new quarters begin. What I mean by this is if today is April 1, 2018 the current quarter is now Q2 2018 and Q3 2016 would to be the last quarter. On March 31, 2018 the current quarter is Q1 2018 and the last quarter in my example would be Q2 2016.
3. The reasoning for this approach is so that a measure can be created and used to force relationships in the data to display sections of the data that have no data intersections. The formula I would use is this ---Example MEASURE = IF(ISBLANK(COUNT('Table'[Column])),0,COUNT('Table'[Column]))---Why I would use this example measure is because Power BI is terrible at recognizing dates and periods of data that have no data intersections. It just won't connect the dots so to speak like Excel does. If, for example, I have data for a city or whatever between Q1 2017 and Q4 2017 and if there was no data for Q2 or Q3 it will not plot the points on a chart. In other words it will not plot the data points for all 4 quarters it will only display data for Q1 and Q4 and this just looks awful and extremely disjointed and very misleading to those who consume the report! It would be the same thing for months. If you had 5 months with no data and all of the other months had data it will not connect all 12 months, just the months that have data.
4. There needs to be the ability for the table to understand what a quarter is and when the new quarter begins the last one drops off and the next one in line is the last new quarter as mentioned above.
5. This implies likely that another table has to be built to recognize the quarters and for some kind of relationship to be setup with the data you are primarily using I assume.

I hope I have explained myself well enough. Any basic data set would work in this situation. I can certainly create some dummy data to help get the solution moving. Surely, someone is smart enough to come up with a solution in this community for this very likely problem! Maybe this just cannot be done at all no matter what the approach? I have yet to come across anything online either in these forums or via blogs that works as expected. I am completely stumped here! I know for a fact that whatever solution someone can come up (if it is the correct solution in the end) will help the community out tremendously!

Any help is appreciated!

Thanks,

calgary_raptor

[Greg_Deckler · Accepted solution]

OK, I took my Rolling Weeks and Rolling Months and created some measures for Rolling Quarter. I'll add it to the Quick Measures gallery soon but here it is in the mean time. This approach uses measures to create dynamic tables in memory that are used within calculations. I believe this is superior to a table as you describe as tables are static once data is loaded.

First you need these two measures for calculating beginning and end of quarters. This approach is a bit overkill for this purpose but allows for the fact that not everyone will necessarily be on standard calendar quarters so you can adjust it in the formula but you could also probably get away with an easier formula for this. Regardless:

mEnd of Quarter

mEnd of Quarter = 
//Get information about the current date
VAR myDate = MAX('Calendar'[Date])
VAR myQuarter = ROUNDUP(MONTH(myDate)/3,0)
VAR myYear = YEAR(myDate)
//Set min and max for Calendar table
VAR minDate = CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'))
VAR maxDate = CALCULATE(MAX('Calendar'[Date]),ALL('Calendar'))
//Create calendar table and add required columns
VAR dateTable = CALENDAR(minDate,maxDate)
VAR dateTable1 = ADDCOLUMNS(dateTable,"Quarter",ROUNDUP(MONTH([Date])/3,0))
//Return date that matches the current year and weeknum and is a week day of 7 (Saturday)
VAR QuarterEndDate = MAXX(FILTER(dateTable1,YEAR([Date])=myYear&&[Quarter]=myQuarter),[Date])
//Return a nicely formatted week ending format "W# Week Ending mm/dd/yyyy"
RETURN QuarterEndDate

mStart of Quarter

mStart of Quarter = 
//Get information about the current date
VAR myDate = MAX('Calendar'[Date])
VAR myQuarter = ROUNDUP(MONTH(myDate)/3,0)
VAR myYear = YEAR(myDate)
//Set min and max for Calendar table
VAR minDate = CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'))
VAR maxDate = CALCULATE(MAX('Calendar'[Date]),ALL('Calendar'))
//Create calendar table and add required columns
VAR dateTable = CALENDAR(minDate,maxDate)
VAR dateTable1 = ADDCOLUMNS(dateTable,"Quarter",ROUNDUP(MONTH([Date])/3,0))
VAR dateTable2 = ADDCOLUMNS(dateTable1,"Day",DAY([Date]))
//Return date that matches the current year and quarter and is a day number of 1
VAR QuarterStartDate = MINX(FILTER(dateTable2,YEAR([Date])=myYear&&[Quarter]=myQuarter&&[Day]=1),[Date])
//Return a nicely formatted week ending format "W# Week Ending mm/dd/yyyy"
RETURN QuarterStartDate

Here is the code to calculate the beginning and ending of rolling quarters (in my case 3 rolling quarters back so including the current quarter a rolling 4 quarter window).

Rolling Quarter End

Rolling Quarter End = 
VAR DateFrom = MAX([Date])
VAR QuartersBack = 0
VAR myQaurter = ROUNDUP(MONTH(DateFrom)/3,0)
VAR myYearQuarter = VALUE(CONCATENATE(YEAR(DateFrom),FORMAT(myQaurter,"##")))
VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar'))
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"QuarterEnding",[mEnd of Quarter],"QuarterStarting",[mStart of Quarter])
VAR LookupDate = EOMONTH(DateFrom,-1*QuartersBack*3)
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate)
RETURN MAXX(tmpCalendar2,[QuarterEnding])

Rolling Quarter Start

Rolling Quarter Start = 
VAR DateFrom = MAX([Date])
VAR QuartersBack = 3
VAR myQaurter = ROUNDUP(MONTH(DateFrom)/3,0)
VAR myYearQuarter = VALUE(CONCATENATE(YEAR(DateFrom),FORMAT(myQaurter,"##")))
VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar'))
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"QuarterEnding",[mEnd of Quarter],"QuarterStarting",[mStart of Quarter])
VAR LookupDate = EOMONTH(DateFrom,-1*QuartersBack*3)
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate)
VAR retValue = MAXX(tmpCalendar2,[QuarterStarting])
RETURN IF(ISBLANK(retValue),MINX(tmpCalendar1,[QuarterStarting]),retValue)

And this is how you could use it all together:

Rolling Quarter Sales = 
VAR rollquarterstart = [Rolling Quarter Start]
VAR rollquarterend = [Rolling Quarter End]
VAR tmpTable = ALL('Sales')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollquarterstart&&[Date]<=rollquarterend)
RETURN SUMX(tmpTable1,[Value])

[Greg_Deckler]

Seems like my Quick Measures for Rolling Months and Rolling Weeks would be a good model to base this off of. If I get some time, I will try to create one for Rolling Quarters. But, in the mean time here are the links:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499

You might also find this one useful:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Quarter/m-p/391541

[Anonymous]

Hi ,

Can we do Same things with Date Slicer. My satrt date should not be hardcoded like todays date it should come from Slicer.

---

@Greg_Deckler wrote:

Seems like my Quick Measures for Rolling Months and Rolling Weeks would be a good model to base this off of. If I get some time, I will try to create one for Rolling Quarters. But, in the mean time here are the links:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499

You might also find this one useful:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Quarter/m-p/391541

---