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

 

ProjectReporting PeriodOutput
A01/01/2021100
B01/01/202180
C01/01/2021150
A01/02/2021120
B01/02/202190
C01/02/2021140
A01/03/2021110
B01/03/202195
C01/03/2021140
 etc.  

 

 

 

 

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.

18 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    4 years ago

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

    • GJUDGE's avatar
      GJUDGE
      Helper II
      4 years ago

      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!

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

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

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

        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