Measure that ignores the current quarter

[PaulCo]

I am looking to create a measure which calculates the coverage ratio for Pipeline/Bookings over the last 4 quarters. I want it to ignore any data in the most current quarter.

I would like this to update automatically so after a quarter is closed it is brought into the latest measure and the 5th previous month is no longer used. 

I have a date table which my pipeline and bookings tables are linked to:

Date	Month Number	Month Name	Short Month Name	Fiscal Year	Fiscal Quarter	Fiscal Month Number
10/1/2014	10	October	Oct	FY15	Q1	1
10/2/2014	10	October	Oct	FY15	Q1	1
10/3/2014	10	October	Oct	FY15	Q1	1

 This is the measure that I am using but it currently uses all data:

Coverage Ratio = SUM('Pipeline'[Quarter Pipe]) / SUM('Bookings'[Booked Amount])

I would think that I needed to set a variable for todays date and then filter out that quarter. But how do I go about doing this?

[parry2k]

@PaulCo Assumed you already have calendar dimension in your model with quarter in it. The way I achieve this in the past, I added a calculated column in date dimension called "Quarters Passed" with current quarter as 0 and previous quarter as 1 and so on...

Once it is done, I filtered where Quarters PAssed >= 1 and <= 4, it will always give me last 4 quarters excluding the current one.

I hope it is helpful. If you need help to create calculated column for "Quarter Passed", let me know.

[PaulCo]

HI @parry2k

Yes I have a calendar dimension with quarter in it. How do I specify what the current quarter is?

Thanks for your assistance. 

[parry2k]

Her e is what you can use for calculated columns

Quarters Passed =
 if (
    Table1[Date] >= today(), 
    -(datediff( TODAY(), Table1[Date], QUARTER)), 
    datediff(Table1[Date], TODAY(), QUARTER)
)

[PaulCo]

Thanks @parry2k, now I have a column in my date table for Quarters Passed which I can use utilize in lots of different ways in my dashboard. 

If I want to create a measure now to find the coverage ratio using this new date column for the last 4 quarters, should I be using a SUMX with Filter? As the date table and pipeline and bookings tables are different I wasn't sure how to properly filter the expression. 

[parry2k]

I guess your data table have relation to date table

[PaulCo]

Yes, the pipeline and bookings table are linked to the dates table. 

[PaulCo]

So the last thing that I need to do is create the measure and apply the filter. But I'm having trouble as the date table where the quarters passed field is seperate to the bookings and pipeline tables. 

The measure without the filter is 

Coverage Ratio = SUM('Pipeline'[Quarter Pipe]) / SUM('Bookings'[Booked Amount])

I would usually filter these using SUMX(Filter( but it won't allow me because it is in a different table and won't give me the option. 

Does anyone know how I can apply the filter to the above measure to only apply to data from the previous 4 quarters only from the dates table?