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

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