How to create calculated column to display current quarter & find the quarter from the date field?

[Charu]

Hi Everyone,

1.To get the current quarter I have used the below DAX formula, the result I should get is "QTR 1" instead I'm Getting "QTR 2".

CurrentQuarter = CONCATENATE("Qtr" & " ",ROUNDUP(MONTH(TODAY())/3,0))

2. To calculate the quarter from the Date field When I tried to duplicate the column via edit query option I'm Getting the following error

I got the below error when I tried this formula 

TD Quarter=CONCATENATE("Qtr" & " ",ROUNDUP(MONTH('Query1'[Transaction_Date])/3,0))

  Here Is how I have connected Oracle Database Please help me to resolve this

Thanks in advance

[v-yuezhe-msft]

@Charu,

I change your DAX to the following and get Qtr 1.

CurrentQuarter = CONCATENATE("Qtr" & " ",ROUNDDOWN(MONTH(TODAY())/3,0))

For your dax formula error, I am able to reproduce it when using ROUNDUP function, however, the DAX works when changing ROUNDDOWN or ROUND function.

Regards,
Lydia

[Charu]

Hi @v-yuezhe-msft

I have tried both ROUNDDOWN and ROUND function as per your suggestion, but I'm getting From JAN to DEC QTR 1.

[v-yuezhe-msft]

@Charu,


You use Today() in the above DAX, I think your formula should be as follows.

TD Quarter=CONCATENATE("Qtr" & " ",ROUND(MONTH('Query1'[Transaction_Date])/3,0))

Regards,
Lydia

[Charu]

HI @v-yuezhe-msft

I forget to inform you one thing that is In my report I'm Having 10reports each connected to 10 different tables so I have created DateTable and added the relationship with each table.

so as per my requirement how to create condition column to get the filter in all the reports.

NOTE: QTR 1 ---APR to JUNE | QTR 2 ---->JULY to SEP | QTR 3 --->OCT to DEC | QTR 4 ---> JAN to Mar

for your reference the columns I have created are already there in this chain 

[Charu]

1. filter one

As I'm going to use direct query mode I'm thinking to maintain one period table in which I will have the data like below

and another 10 tables I will be having one date column and the qtr number column so that I can create the relationship from period table to all 10 tables.

so If I use period table Qtrnumber column as filter whether all the table data would filter the related quarter data?for example, if I pick Qtr 1 the related April to June data should display?

2.Filter 2

Just want to display current qtr number that is from april to june qtr 1 this I should get from today() function.I have already explained the issue I'm facing 

[Charu]

Hi everyone,

In my requirement number one, I could achieve as I expected All the data are filtered as per my qtr number selection.

The Issue I'm facing is the filter 2 that is I have created the calculated column to get current qtr for that I have used Round down function and when I select the filter there are No changes in my data means It's not filtered.

Current Qtr = CONCATENATE("Qtr" &" ",ROUNDDOWN(MONTH(TODAY())/3,0))

How to display the current quarter data by default?

For your information!

Please help me to get work this current qtr filter.I have tested the round down returns QTR 1 to all the months that is from Jan to dec returns QTR 1

[Greg_Deckler]

@Charu- This is brute for but should work.

Current Quarter = SWITCH(
                        MONTH(TODAY()),
                        4,"Qtr1",
                        5,"Qtr1",
                        6,"Qtr1",
                        7,"Qtr2",
                        8,"Qtr2",
                        9,"Qtr2",
                        10,"Qtr3",
                        11,"Qtr3",
                        12,"Qtr3",
                        1,"Qtr4",
                        2,"Qtr4",
                        3,"Qtr4",
                        "Invalid")

[Charu]

HI @Greg_Deckler

I tried the Formula which you suggested but my data is not filtering, is there any settings to do to make it work?

Please have a look at this below result 

[Charu]

Hi @Greg
I need the filter should selected by default based on the hard coded current quarter. So that I could display only the current quarter data...

[Phil_Seamark]

HI @Charu

Why not do the calcuation in the SQL statement against the Oracle DB?

[Charu]

Hi @Phil_Seamark

I need the current quarter as a filter in my report

[Phil_Seamark]

Hi @Charu

The reason you are getting "Qtr 2" when using TODAY() is because we are now in April, which happens to be in "Qtr 2"

[Charu]

As Per Financial year in India Apr to Jun falls in qtr1, Help me how to get it?

I have created the table from modeling tab like 

Date Table =
ADDCOLUMNS (
CALENDAR (DATE(1980,1,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),"Month",MONTH([Date])
)

then Calculated column

Quarter = IF('Date'[Month]=1,"QTR 4",IF('Date'[Month]=2,"QTR 4",IF('Date'[Month]=3,"QTR 4",IF('Date'[Month]=4,"QTR 1",IF('Date'[Month]=5,"QTR 1",IF('Date'[Month]=6,"QTR 1",IF('Date'[Month]=7,"QTR 2",IF('Date'[Month]=8,"QTR 2",IF('Date'[Month]=9,"QTR 2",IF('Date'[Month]=10,"QTR 3",IF('Date'[Month]=11,"QTR 3",IF('Date'[Month]=12,"QTR 4"))))))))))))

How to create DAX formula to compare the current quarter with the Calculated column"Quarter" and Display QTR1 for APRIL month

Calculated column for current qtr I used 

Current qtr = ROUNDUP(MONTH(TODAY())/3,0)

if this returns month num as 4 then should display as QTR 1

[Charu]

Hi @Phil_Seamark

This quarter may vary according to state right, then How could we achieve it?

[Charu]

I couldn't create dax formula to get the current quarter from the column "TransactionDate"