topic Re: Last N quarters from selected quarter and year in DAX Commands and Tips

Last N quarters from selected quarter and year

siva54 — Mon, 05 Jun 2023 10:24:41 GMT

Hi Team,

I have requirement that when i'm selected quarter, bar graph need to show last 5 quarters from selected quarter.

I have sales table and i need to check how many sales are happend in last 5 quarters.

For example,

I have year Slicer and Quarter Slicer, i selected 2023 qtr 1 then i need to show
qtr1-23,
qtr4-22,
qtr3-22,
qtr2-22,
qtr1-22

Regards,
Siva

Re: Last N quarters from selected quarter and year

johnt75 — Mon, 05 Jun 2023 13:12:29 GMT

Have a look at https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/.

Re: Last N quarters from selected quarter and year

siva54 — Tue, 06 Jun 2023 04:16:13 GMT

Hi Johnt75,

It is working for monthly scenario, when it comes to Quarter it is not working. Could you please help me.

Re: Last N quarters from selected quarter and year

johnt75 — Tue, 06 Jun 2023 08:14:28 GMT

The principle should be the same, you would need to modify the measure or calculation item to take into account quarters rather than months.

Re: Last N quarters from selected quarter and year

siva54 — Tue, 06 Jun 2023 10:17:44 GMT

johnt75 ,

I have used this dax

Last4Qtrs =

VAR NumOfqtr =-4

VAR ReferenceDate = MAX ( 'Date'[Date] )

VAR PreviousDates =

DATESINPERIOD (

'Previous Date'[Date],

ReferenceDate,

NumOfqtr,

QUARTER

)

VAR Result =

CALCULATE (AVERAGE('WB Tbl_R2R_SLA_Trend'[Actual_Value]),

REMOVEFILTERS ( 'Date' ),

KEEPFILTERS ( PreviousDates ),

USERELATIONSHIP ( 'Previous Date'[Date], 'Date'[Date] )

)

RETURN Result

I'm getting an extra quarter like this

Re: Last N quarters from selected quarter and year

johnt75 — Tue, 06 Jun 2023 11:07:17 GMT

Check that the date column in your fact table, which is linked to the date table, is of type date and not datetime. You'll need to check in both Power Query and in Power BI desktop.