Power BI get dynamic serial quarter number for date

Question

I have the following data in power BI

declare @tbl table (Dates date)
insert into @tbl values
('2021-01-01'),('2021-02-01'),('2021-03-01'),('2021-04-01'),('2021-05-01'),('2021-06-01'),
('2021-07-01'),('2021-08-01'),('2021-09-01'),('2021-10-01'),('2021-11-01'),('2021-12-01'),
('2022-01-01'),('2022-02-01'),('2022-03-01'),('2022-04-01'),('2022-05-01'),('2022-06-01'),
('2022-07-01'),('2022-08-01'),('2022-09-01'),('2022-10-01'),('2022-11-01'),('2022-12-01'),
('2023-01-01'),('2023-02-01'),('2023-03-01'),('2023-04-01'),('2023-05-01'),('2023-06-01'),
('2023-07-01'),('2023-08-01'),('2023-09-01'),('2023-10-01'),('2023-11-01'),('2023-12-01')
SELECT * FROM @tbl

I want to create calculated column that will arrange my quarters in a serial number from current quarter. For example since today’s date is 28/04/2023 this will be 1, the previous quarter will be 2, the one before that will be 3 etc. I wrote the DAX below, however any future quarter will be blank

MY DAX

Quarter Serial Number = if(year(TODAY())= year('TBL'[Dates]) && MONTH('TBL'[Dates]) <4, 1,
             IF(year(TODAY())=  year('TBL'[Dates]) && MONTH('TBL'[Dates]) <7, 2,
              IF(year(TODAY())=  year('TBL'[Dates]) && MONTH('TBL'[Dates]) <10, 3,
              IF(year(TODAY())=year('TBL'[Dates]) && MONTH('TBL'[Dates]) <13, 4,
               IF(year(TODAY())-1=year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) <4, 5,
              IF(year(TODAY())-1=year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {4,5,6}, 6,
              IF(year(TODAY())-1= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {7,8,9}, 7,
              IF(year(TODAY())-1= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {10,11,12}, 8,
              IF(year(TODAY())-2= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {10,11,12}, 9,
              IF(year(TODAY())-2= year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {1,2,3}, 10,
              IF(year(TODAY())-2= year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {4,5,6}, 11,
             IF(year(TODAY())-2=year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {7,8,9}, 12,
               IF(year(TODAY())-3=year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {10,11,12}, 13
               )))))))))))))

Current output

quarter 5,6,7,8,10,11, 12 and 13 are missing

Desired Output

Thanks

score 1 · Accepted Answer · answered Apr 28 '23 at 18:22

Don't use IF statements like that - you'll cause a brain haemorrhage

Quarter Serial = 
IF(TBL[Dates] < TODAY(), 
    VAR t = 
    ADDCOLUMNS(
            CALENDAR(TBL[Dates], TODAY()),
            "@q", YEAR([Date])*10+ QUARTER([Date])
    )
    VAR t2 = 
    ADDCOLUMNS(
        t, 
        "@c",  
        VAR a = [@q]
        RETURN
        COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER(t, [@q] >= a), "@s", [@q]) )))
    RETURN 
    CALCULATE( MAXX(t2, [@c]), FILTER(t2, [@c] = TBL[Dates]))
)

Power BI get dynamic serial quarter number for date

1 Answers1