Comparing Periods in the same table

[GJUDGE]

I am sure this is a fairly straightforward problem for most but I'm fairly new to Power BI and any help is appreciated!

I have a single table that compiles monthly reporting information. I want to be able to calculate the difference between current month Output and Previous Month Output. What I can't figure out is how to do this dynamically so that each time a new month is added, the measure will compare the max reporting period with the max minus one?

Thanks in advance!

Project	Reporting Period	Output
A	01/01/2021	100
B	01/01/2021	80
C	01/01/2021	150
A	01/02/2021	120
B	01/02/2021	90
C	01/02/2021	140
A	01/03/2021	110
B	01/03/2021	95
C	01/03/2021	140
	etc.

[jppv20]

hI @GJUDGE ,

You can create this measure:

OutputDifference =

var Output = SUM('Table'[Output])

var OutputLastPeriod = CALCULATE(SUM('Table'[Output]),ALLEXCEPT('Table','Table'[Project]),PREVIOUSMONTH('Table'[Reporting Period]))

Return

Output-OutputLastPeriod

This will give the following result:

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@jppv20 This isn't quite giving me what I need (but I appreciate your help!). When I put the measure into matrix view I get the following, which appears to be summing all the output months. How can I adapt the measure to only show the change for the latest month? i.e. -10 for project A, 5 for project B and 0 for project C with a total of -5?

[jppv20]

@GJUDGE Thanks for clarifying, I understand the issue better now. Do you need this to be solved in the measure? Otherwise you can add a date filter to select the month for which you want to see the change:

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@jppv20 Thanks! If this can be built into the measure that would be best if you could advise?

[jppv20]

@GJUDGE 

In that case this measure should work:

OutputDifference =

var Output = CALCULATE(SUM('Table'[Output]),FILTER('Table','Table'[Reporting Period]=MAXX('Table','Table'[Reporting Period])))

var OutputLastPeriod = CALCULATE(SUM('Table'[Output]),FILTER('Table',MONTH('Table'[Reporting Period])=MONTH(MAXX('Table','Table'[Reporting Period]))-1))

Return

Output-OutputLastPeriod

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@jppv20  The measure is giving me the total of the output column as opposed to the output difference column. I have copied my measure below with actual table names etc., if you can see any errors within this?

Monthly MW Change =

var Output = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data','Master Data'[Reporting Period]=MAXX('Master Data','Master Data'[Reporting Period])))

var OutputLastPeriod = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data',MONTH('Master Data'[Reporting Period])=MONTH(MAXX('Master Data','Master Data'[Reporting Period]))-1))

Return

Output-OutputLastPeriod

[GJUDGE]

@jppv20 Sorry, it actually works! I still had a filter on!! Thank you for your help! Could you help me understand how would I modify this measure to then compare the start of the year (with a fixed date rather than reporting period included?)

to current month?

[jppv20]

@GJUDGE Great that it works!

For comparisson with the start of the year you can use this measure:

Start of year Change =

var Output = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data','Master Data'[Reporting Period]=MAXX('Master Data','Master Data'[Reporting Period])))

var OutputLastPeriod = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data',YEAR('Master Data'[Reporting Period])=YEAR(TODAY())&&MONTH('Master Data'[Reporting Period])=MONTH(MINX('Master Data','Master Data'[Reporting Period]))))

Return

Output-OutputLastPeriod

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@jppv20 Hi Jori. This seems to be bringing back the total for the current month only and not deducting the values from the start of the year? Is there anything else I need to modify to the OutputLastPeriod element? 

Thanks,
Gary 

[jppv20]

@GJUDGE Hi Gary. Are you sure you are not filtering anything? It gives me this result:

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@JP Sadly no filters are on! Could it be an issue that my "start of the year" value is actually in Dec 2020 (so in reporting period column it is 01/12/2020)?

[jppv20]

@GJUDGE I think that would be the problem indeed. In that case try:

Start of year Change =

var Output = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data','Master Data'[Reporting Period]=MAXX('Master Data','Master Data'[Reporting Period])))

var OutputLastPeriod = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data',YEAR('Master Data'[Reporting Period])=YEAR(TODAY())-1&&MONTH('Master Data'[Reporting Period])=MONTH(MAXX('Master Data','Master Data'[Reporting Period]))))

Return

Output-OutputLastPeriod

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[GJUDGE]

@jppv20 Hi Jori, still providing me the same result - only the total output from the current month with no deduction for the start of the year 😞. I've managed to correct by applying the following, but not sure if this will work going forward into other years!

Yearly MW Change = var Output = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data','Master Data'[Reporting Period]=MAXX('Master Data','Master Data'[Reporting Period])))

var OutputLastPeriod = CALCULATE(SUM('Master Data'[Projected Capacity (MW)]),FILTER('Master Data',('Master Data'[Reporting Period])=MINX('Master Data','Master Data'[Reporting Period])))

Return

Output-OutputLastPeriod

[jppv20]

@GJUDGE 

I think in this case it will always compare to the December 2020 value, also in a couple of years.

Could you share some more of the table so I can replicate the problem?

Jori

[GJUDGE]

@jppv20 I'm happy to stick with the current solution for the time being. Will need to remember that at the start of a new year though! Can I ask you one other question - how would I write a simple measure to sum the output for the previous month (i.e. max period minus 1)?

[v-xiaotang · Accepted solution]

Hi @GJUDGE 

You can use Edate() function to get last period, for example, 

Last Period = 
    var _currentDate=MAX('table'[date])
    var _lastDate=EDATE(_currentDate,-1)
return 
    CALCULATE(SUM('table'[Output]),'table'[date]=_lastDate)

Kindly note:  Edate() function has some limitations,

If you can't use Edate() on some occasions, you can try this measure,

Last Period = 
    var _maxDate=MAX('table'[date])
    var _lastDate=DATE(YEAR(_maxDate),MONTH(_maxDate)-1,1)
return 
    CALCULATE(SUM('table'[Output]),'table'[date]=_lastDate)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

so you saying you want to sum this months and sum last month and then work out the difference as a change %?

[GJUDGE]

Not even a % change, just the difference, but my main worry is not needing to update the measure each month every time a new months worth of data is added. Any tips are welcomed!