Forum Discussion

GJUDGE's avatar
GJUDGE
Helper II
4 years ago
Solved

Comparing Periods in the same table

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 t...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

GJUDGE's avatar
GJUDGE
Helper II
4 years ago

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's avatar
jppv20
Solution Sage
4 years ago

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

 

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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.

  • GJUDGE's avatar
    GJUDGE
    Helper II
    4 years ago

    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's avatar
    jppv20
    Solution Sage
    4 years ago

    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's avatar
    GJUDGE
    Helper II
    4 years ago

    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's avatar
    jppv20
    Solution Sage
    4 years ago

    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's avatar
    GJUDGE
    Helper II
    4 years ago

    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's avatar
    jppv20
    Solution Sage
    4 years ago

    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's avatar
    GJUDGE
    Helper II
    4 years ago

    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)?