Quarter in DAX

[Anonymous]

DAXHi

need help on growth in quarter.

below is my requirement Q4(current quarter)  =Q3-Q2,

today_date:= roundup(month(today())/3,0)

LOB Growth For Quarter2:= CALCULATE(SUMX('TAFB_FACT_TRANSACTION_DETAIL','TAFB_FACT_TRANSACTION_DETAIL'[FEE_AMT_AFTER_TAX])*SUMX('TAFB_DIM_EXCHANGE_RATES','TAFB_DIM_EXCHANGE_RATES'[RATE]), DATEADD(DISTINCT(TAFB_FACT_TRANSACTION_DETAIL[Today date]),-2,QUARTER))

LOB Growth For Quarter test:=[LOB Growth For Quarter2] - [LOB Growth For Quarter1]

when i am executing above query, i am getting balnk value, no error. 

would some

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You could create a column to define which quarter the date is,

q = ROUNDUP(MONTH([Date])/3,0)

To get the result,  Q4(current quarter)  =Q3-Q2, i create measures as below

q3 =
IF (
    SUM ( Table1[value] ) <> BLANK ()
        || SUM ( Table2[rate] ) <> BLANK (),
    CALCULATE (
        SUM ( Table1[value] ) * SUM ( Table2[rate] ),
        FILTER ( ALL ( 'date table' ), ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) = 3 )
    )
)

q2 =
IF (
    SUM ( Table1[value] ) <> BLANK ()
        || SUM ( Table2[rate] ) <> BLANK (),
    CALCULATE (
        SUM ( Table1[value] ) * SUM ( Table2[rate] ),
        FILTER ( ALL ( 'date table' ), ROUNDUP ( MONTH ( [Date] ) / 3, 0 ) = 2 )
    )
)

current = [q3]-[q2]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Juanli,

Thanks a lot, the solution was great help, the query matches our requirement. we need small change. 

below are new columns in table

current Quarter= roundup(month(today())/3,0)

previous quarter = roundup(month(today())/3,0)-1

pre to pre Quarter= roundup(month(today())/3,0)-2

Qtr= = ROUNDUP(MONTH('TAFB_FACT_TRANSACTION_DETAIL'[VALUE_DATE])/3,0) 

here Value_date is date from table.

Growth for Quarter2:= CALCULATE(SUM('TAFB_FACT_TRANSACTION_DETAIL'[FEE_AMT_AFTER_TAX]) * SUMX('TAFB_DIM_EXCHANGE_RATES'[RATE]),TAFB_FACT_TRANSACTION_DETAIL[Qtr] = VALUES('TAFB_FACT_TRANSACTION_DETAIL'[Pre to Pre Quarter]))

Growth for Quarter3:= CALCULATE(SUM('TAFB_FACT_TRANSACTION_DETAIL'[FEE_AMT_AFTER_TAX]) * SUMX('TAFB_DIM_EXCHANGE_RATES'[RATE]),TAFB_FACT_TRANSACTION_DETAIL[Qtr] =                            VALUES('TAFB_FACT_TRANSACTION_DETAIL'[Previous Quarter]))

current=Growth for Quarter3 - Growth for Quarter2

output:

LOB_NAMES,Growth for Quarter2,Growth for Quarter3,Growth for Quarter

marketing	355509.97	378366.09	-22856.12
sales	318397.95	319050.13	-652.18
production	535846.95	534775.02	1071.93
manufacturing	448158.06	598541.53	-150383.47

but there is small flaw in my requirement is 

Quarter from today() function.

4-1=3

3-1=2

2-1=1

1-1=0 <-- this is my flaw, i have to over come. i mean when we are in quarter2 my calculation won't work. 

your query exactly matches requirement when i am taking date column from existing table. 

my requirement is we have to calculate based on current date. i mean today() or now()

would you please help me.

Thanks a lot for your great help 🙂