cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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]

Capture11.JPGCapture12.JPG

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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]

Capture11.JPGCapture12.JPG

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
Not applicable

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 🙂 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors