Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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]
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]
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 🙂
User | Count |
---|---|
91 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |