Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter Latest Quarter

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.

DAX
Filter
Quarter
  • Anonymous's avatar
    Anonymous
    5 years ago
    [Rebate Current Quarter] =
CALCULATE(
	[Rebate Sum],
	CALCULATETABLE(
		TOPN(1,
			SUMMARIZE(
				YourFactTable,
				'Date'[FY Year & Quarter]
			),
			'Date'[FY Year & Quarter],
			DESC
		),
		ALL( YourFactTable )
	),
	ALL( 'Date' )
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    [Rebate Current Quarter] =
CALCULATE(
	[Rebate Sum],
	CALCULATETABLE(
		TOPN(1,
			SUMMARIZE(
				YourFactTable,
				'Date'[FY Year & Quarter]
			),
			'Date'[FY Year & Quarter],
			DESC
		),
		ALL( YourFactTable )
	),
	ALL( 'Date' )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago
        [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...