Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have following table:
Sales | COGS | Profit | |
January | 1000 | 500 | 500 |
February | 1000 | 500 | 500 |
March | 2000 | 500 | 1500 |
April | 3000 | 500 | 2500 |
May | 4000 | 500 | 3500 |
June | 5000 | 500 | 4500 |
July | 6000 | 500 | 5500 |
August | 6000 | 1000 | 5000 |
September | 6000 | 1000 | 5000 |
October | 6000 | 1000 | 5000 |
November | 6000 | 1000 | 5000 |
December | 6000 | 1000 | 5000 |
Now i want to create
1. a new measure of sum of profit from March to December forecast.
2. Another measure of sum of profit from January to February as an actual value.
Then put these two new measure together in a line chart to see the actual and forecast together.
I am bit lost about how to do this, could anyon please help? Thank you in advance 🙂
Solved! Go to Solution.
@Anonymous ,
Hi there,
You can write the measures like that:
m_ActualProfit = if( min('Table'[Month]) in {"January","February"}, CALCULATE(SUM('Table'[Profit])))
m_ForecastProfit = if( NOT(MIN('Table'[Month]) IN {"January","February"}), CALCULATE(SUM('Table'[Profit])))
And then display them in a chart like that:
BUT, I would like to suggest few enhancements to the way you are shaping the data in your data model, here are some pointers:
1- Try to use an actual date instead of the month name, the reason for that is that it makes it easier to do the following:
a- Write dynamic date filters instead of static ones, for example the m_ActualProfit could then be written like that:
m_ActualProfit = if( MIN('Table'[MonthAsDate]) < DATE(YEAR(NOW()),MONTH(NOW()),1), CALCULATE(SUM('Table'[Profit])))
b- To be able to get the months sorted correctly in the chart instead of having to explicitly add a Month No column and use it to sort Month name.
2- You can even go a bit further and create a date dimension in your data model if you are going to build more complex reporting based on that data model.
Refs:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time
Hope this helps.
Best Regards,
Haitham
@Anonymous ,
Hi there,
You can write the measures like that:
m_ActualProfit = if( min('Table'[Month]) in {"January","February"}, CALCULATE(SUM('Table'[Profit])))
m_ForecastProfit = if( NOT(MIN('Table'[Month]) IN {"January","February"}), CALCULATE(SUM('Table'[Profit])))
And then display them in a chart like that:
BUT, I would like to suggest few enhancements to the way you are shaping the data in your data model, here are some pointers:
1- Try to use an actual date instead of the month name, the reason for that is that it makes it easier to do the following:
a- Write dynamic date filters instead of static ones, for example the m_ActualProfit could then be written like that:
m_ActualProfit = if( MIN('Table'[MonthAsDate]) < DATE(YEAR(NOW()),MONTH(NOW()),1), CALCULATE(SUM('Table'[Profit])))
b- To be able to get the months sorted correctly in the chart instead of having to explicitly add a Month No column and use it to sort Month name.
2- You can even go a bit further and create a date dimension in your data model if you are going to build more complex reporting based on that data model.
Refs:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
https://docs.microsoft.com/en-us/power-bi/desktop-auto-date-time
Hope this helps.
Best Regards,
Haitham
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |