topic Re: Filter Latest Quarter in DAX Commands and Tips

Filter Latest Quarter

Anonymous — Fri, 16 Jul 2021 12:23:06 GMT

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.

Re: Filter Latest Quarter

Anonymous — Fri, 16 Jul 2021 21:32:31 GMT

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

Re: Filter Latest Quarter

Anonymous — Mon, 19 Jul 2021 12:05:39 GMT

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?

Re: Filter Latest Quarter

Anonymous — Mon, 19 Jul 2021 12:18:02 GMT

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

Re: Filter Latest Quarter

Anonymous — Mon, 19 Jul 2021 12:39:39 GMT

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