Calculate the sum of specifit rows

[Anonymous]

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 🙂

[helassal · Accepted 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