Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |