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.
Hi,
I am trying to calculate a value where I am trying to place it in a matrix visual where In columns we have Quarter, In rows we have locations and In values I need a measure of this sort :
If it is Q1 = Q1(Value1) - Q4(Value2)
Q2 = Q2(Value1) - Q1(Value1)
Q3 = Q3(Value1) - Q2(Value1)
Q4 = Q4(Value1) - Q3(Value1)
For, Q2,Q3,Q4 it's simple current quarter - previous quarter formula. But I am not getting how to get the Q1.
I have tried these dax but it wasn't working.
Anything of help would be great!
Thanks in Advance!
Kamal
Solved! Go to Solution.
Hi @kamalbandam ,
First create a calendar table as below:
dim table = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Quarter","Q"&QUARTER(''[Date]))
Then create a measure as below:
Measure =
VAR currentquater=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Quarter]=MAX('Table'[Quarter])&&YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))))
var _previous=CALCULATE(SUM('Table'[Value]),DATEADD('dim table'[Date],-1,QUARTER))
Return
currentquater-_previous
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @kamalbandam ,
First create a calendar table as below:
dim table = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Quarter","Q"&QUARTER(''[Date]))
Then create a measure as below:
Measure =
VAR currentquater=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Quarter]=MAX('Table'[Quarter])&&YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))))
var _previous=CALCULATE(SUM('Table'[Value]),DATEADD('dim table'[Date],-1,QUARTER))
Return
currentquater-_previous
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |