Forum Discussion
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 quart...
[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?
[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
ResultThe 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.