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.

jppv20's avatar
jppv20
Solution Sage
4 years ago

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

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'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

       

      • 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