Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Quarter in DAX

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

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      marketing355509.97378366.09-22856.12
      sales318397.95319050.13-652.18
      production535846.95534775.021071.93
      manufacturing448158.06598541.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 🙂