cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyLich
Regular Visitor

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

AndyLich_0-1626437839778.png

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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
Not applicable

[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...

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors