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 a beginner of power query and have imported a query from SQL and create a matrix visuals. In Values, i drag column "Total Cost". In Columns, i drag the column "Date". As my data contains data in 2019 and 2020, so in matrix visuals it shows in 2 columns. How can I create a measure to show the difference and % change of total cost between 2019 and 2020?
Name 2019 2020 Total
A $20 $25 $45
B $30 $28 $58
C $40 $35 $75
Thanks
Solved! Go to Solution.
Hi @brandonchan ,
Try this:
1. Create a Calendar table.
Calendar = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
2. Create relationship.
3. Create Measures.
Difference =
VAR Current_ =
SUM ( 'Table'[Total Cost] )
VAR Previous_ =
CALCULATE (
SUM ( 'Table'[Total Cost] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
Current_ - Previous_
% Difference =
VAR Current_ =
SUM ( 'Table'[Total Cost] )
VAR Previous_ =
CALCULATE (
SUM ( 'Table'[Total Cost] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
DIVIDE ( Current_ - Previous_, Previous_ )
4. Create a Matrix visual.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @brandonchan ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey
Hi @brandonchan ,
Try this:
1. Create a Calendar table.
Calendar = CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
2. Create relationship.
3. Create Measures.
Difference =
VAR Current_ =
SUM ( 'Table'[Total Cost] )
VAR Previous_ =
CALCULATE (
SUM ( 'Table'[Total Cost] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
Current_ - Previous_
% Difference =
VAR Current_ =
SUM ( 'Table'[Total Cost] )
VAR Previous_ =
CALCULATE (
SUM ( 'Table'[Total Cost] ),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
RETURN
DIVIDE ( Current_ - Previous_, Previous_ )
4. Create a Matrix visual.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If they are a column in your table then you can use
Diff = [2020]-[2019]
But if they are like row values. Or you dow it by using unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
After that year would one column. create a new year table and do it like this
this year =
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Year),Year[Year]=Max(Year[Year])))
last year =
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Year),Year[Year]=Max(Year[Year])))
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |