topic Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER in DAX Commands and Tips

FILTERING DATA FOR THE LAST ENTIRE QUARTER

PieroPinzone95 — Thu, 09 Mar 2023 18:01:35 GMT

Hi guys, i would like to filter my data for the last entire quarter... i mean:
there are only 4 quarters:
gen,feb,mar (first quarter)
apr,may,jun (second quarter)
jul,aug,sep (third quarter)

oct,nov,dec (fourth quarter)
if today is in quarter x, i want the x-1 quarter

so for example if is true that 01/01/2023 < today < 01/04/2023

i want my data filtered between 1/10/2022 and 1/12/2022... tips?

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

FreemanZ — Thu, 09 Mar 2023 18:20:46 GMT

hi PieroPinzone95

there are always better ways, but please try like:

Measure =

VAR _date = MAX(DateTable[Date])

VAR _q = FORMAT ( _date, "Q" )

VAR _y = YEAR (_date)

RETURN

SWITCH(

_q,

CALCULATE(

[...],

DateTable[Date]<=DATE(_y-1, 12, 31),

DateTable[Date]>=DATE(_y-1, 9, 1)

CALCULATE(

[...],

DateTable[Date]<=DATE(_y, 3, 31),

DateTable[Date]>=DATE(_y, 1, 1)

CALCULATE(

[...],

DateTable[Date]<=DATE(_y, 6, 30),

DateTable[Date]>=DATE(_y, 4, 1)

CALCULATE(

[...],

DateTable[Date]<=DATE(_y, 9, 30),

DateTable[Date]>=DATE(_y, 7, 1)

)

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

PieroPinzone95 — Fri, 10 Mar 2023 09:46:53 GMT

Hi Freeman, thanks for your solution... I tried it but it doesn't work... the error message said that the SWITCH function doesn't support comparison between dates and integers... so I have to use the VALUE or FORMAT function to convert one of these two values... below the function I have written: (i need a measure for a barchart)

TargheUltimoTrimestre2 =

VAR _date = TODAY()

VAR _q = FORMAT ( _date, "Q" )

VAR _y = YEAR (_date)

RETURN

SWITCH(

_q,

CALCULATE(

DISTINCTCOUNT(VW_Polizze[IdPolizza]),

VW_Polizze[DataSales]<=DATE(_y-1, 12, 31),

VW_Polizze[DataSales]>=DATE(_y-1, 9, 1)

CALCULATE(

DISTINCTCOUNT(VW_Polizze[IdPolizza]),

VW_Polizze[DataSales]<=DATE(_y, 3, 31),

VW_Polizze[DataSales]>=DATE(_y, 1, 1)

CALCULATE(

DISTINCTCOUNT(VW_Polizze[IdPolizza]),

VW_Polizze[DataSales]<=DATE(_y, 6, 30),

VW_Polizze[DataSales]>=DATE(_y, 4, 1)

CALCULATE(

DISTINCTCOUNT(VW_Polizze[IdPolizza]),

VW_Polizze[DataSales]<=DATE(_y, 9, 30),

VW_Polizze[DataSales]>=DATE(_y, 7, 1)

)

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

AntrikshSharma — Fri, 10 Mar 2023 10:29:43 GMT

PieroPinzone95 What I prefer is to have a Column in the date table that acts as an offset so you only need to perform basic subtraction and addition to current Quarter, Year, Month etc.

Sample date table:

let Source = Table.FromColumns ( { List.Dates ( #date ( 2022, 01, 01 ), 730, Duration.From ( 1 ) ) }, type table [ Date = date ] ), InsertedYear = Table.AddColumn ( Source, "Year", each Date.Year ( [Date] ), Int64.Type ), InsertedQuarter = Table.AddColumn ( InsertedYear, "Quarter", each Date.QuarterOfYear ( [Date] ), Int64.Type ), AddedQuarterOffset = Table.AddColumn ( InsertedQuarter, "QuarterOffset", each ( [Year] * 4 ) - 1 + [Quarter], Int64.Type ) in AddedQuarterOffset

And then a DAX Formula like this:

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

Anonymous — Wed, 28 Feb 2024 12:16:09 GMT

Hi, I have a demand similar to yours, however in my situation, the date column in the table visual should only show data from Prior quarter to quarter.
Suppose if todays date is 28/02/2024 so if I select Prior quarter on Slicer so it should only display dates between from 01/10/2023 to 31/12/2023 as said Quarter to Quarter. Can Someone help me?

I have almost close to my requirement but it is onlu filtering Month of October and November but not able to filter December as shown in image.
I have used these DAX for Prior Quarter
Previous quarter = INT(MAX(Sheet1[Ratification Date]) IN DATESQTD(DATEADD('Date'[Date],-1,QUARTER)))
Please guide me, I don't know where I am going wrong.

topic Re: FILTERING DATA FOR THE LAST *ENTIRE* QUARTER in DAX Commands and Tips

FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

Re: FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

Re: FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

Re: FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

Re: FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

topic Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER in DAX Commands and Tips

FILTERING DATA FOR THE LAST ENTIRE QUARTER

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER

Re: FILTERING DATA FOR THE LAST ENTIRE QUARTER