Filter Latest Quarter

[AndyLich]

Hello

Apologies if this has been answered in another post. I have searched but cannot find the answer I'm looking for.

I'm trying to create a measure to show the sum a value for the latest quarter in the data.

I already have a measure to sum the column I'm interested in

Rebate Sum =
SUM ( vwEdoxabanRebateByCCG[RebateValue] )

 I have joined my fact table to my date table

I've tried the following which isn't giving me the result I'm after

Rebate Current Quarter =
CALCULATE (
    [Rebate Sum],
    FILTER ( 'Date', 'Date'[FY Year & Quarter] = MAX ( 'Date'[FY Year & Quarter] ) )
)

I think its filtering to the last quarter in the Date table. I would like it to filter on the last quarter for which there is data in the fact table (vwEdoxabanRebateByCCG) without adding a quarter column to the fact table.

Thanks in advance for any help.

[Anonymous · Accepted solution]

[Rebate Current Quarter] =
CALCULATE(
	[Rebate Sum],
	CALCULATETABLE(
		TOPN(1,
			SUMMARIZE(
				YourFactTable,
				'Date'[FY Year & Quarter]
			),
			'Date'[FY Year & Quarter],
			DESC
		),
		ALL( YourFactTable )
	),
	ALL( 'Date' )
)

[AndyLich]

Thanks very much worked a treat! I'm going to have to try and understand how you did that 🤔 Would it be easy to amend to get a measure for the previous quarter?

[Anonymous]

[Rebate (Prev to Curr Quarter)] =
var CurrentQuarter =
	CALCULATETABLE(
		TOPN(1,
			SUMMARIZE(
				YourFactTable,
				'Date'[FY Year & Quarter]
			),
			'Date'[FY Year & Quarter],
			DESC
		),
		REMOVEFILTERS( YourFactTable )
	)
var Result =
	CALCULATE(
		[Rebate Sum],
		CALCULATETABLE(
			TOPN(1,
				SUMMARIZE(
					YourFactTable,
					'Date'[FY Year & Quarter]
				),
				'Date'[FY Year & Quarter],
				DESC
			),
			ALL( YourFactTable ),
			'Date'[FY Year & Quarter] <> CurrentQuarter
		)
	)
return
	Result

The logic will return the [Rebate Sum] for the quarter that has any data in it and is prior to the current one. Therefore if you current quarter is 2021-Q3 and there's no data for 2021-Q2, it'll return the value for 2021-Q1 if there is any data in it. I think you get the gist...

[AndyLich]

Thanks again! Worked perfectly. I'm fairly new to DAX so it will take me a while to fully understand it.