FILTERING DATA FOR THE LAST *ENTIRE* QUARTER

[PieroPinzone95]

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? 

[AntrikshSharma]

@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:

[FreemanZ · Accepted solution]

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,

    1,

    CALCULATE(

        [...],

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

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

    ),

    2,

    CALCULATE(

        [...],

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

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

    ),

    3,

    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)

    )

)

[PieroPinzone95]

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,

    1,

    CALCULATE(

        DISTINCTCOUNT(VW_Polizze[IdPolizza]),

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

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

    ),

    2,

    CALCULATE(

        DISTINCTCOUNT(VW_Polizze[IdPolizza]),

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

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

    ),

    3,

    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)

    )

)

[Anonymous]

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.