Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

calculate current month vs previous month

Hi,

 

I need some help on this, I'm pretty new to PBI.

 

I have a list of meter readings and I want to automatically calculate the usages in each month.

The list will be updated on monthly basis via PowerApp and Automate. So every month PBI has to calculate the new month usage automatically.

So, meter reading previous month = begin, meter reading current month = end.

Formula: end - begin = usage.

 

Data looks like this:

Reading dateMeter aMeter b
1-7-202136524562
1-8-202139545021
1-9-202140145234
   

 

Could someone please help me with this (A)

 

Thanks in advance

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Anonymous Yes! So now you can do this:

    Usage Column = 
  VAR __CurrentValue = [Value]
  VAR __Meter = [Attribute]
  VAR __Date = [Datum meteropname]
  VAR __PreviosDate = MAXX(FILTER('Table',[Datum meteropname]<__Date && [Attribute]=__Meter),[Datum meteropname])
  VAR __PreviousValue = MAXX(FILTER('Table',[Datum meteropname]=__PreviousDate && [Attribute]=__Meter),[Value])
RETURN
  __CurrentValue - __PreviousValue

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

     

    If not that, then I'm not sure because your sample data looks weird. Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler 

       

      Hi Greg,

      Thanks for the reply and info in order to help me with this headache...

       

      Here is the sample data:

       

      Date of meter readingMeter AMeter BMeter C
      1-12-2020        687,244     43,924       5,514
      1-1-2021        690,673     45,316       5,514
      1-2-2021        694,075     46,669       5,514
      1-3-2021        697,137     47,880       5,514
      1-4-2021        700,811     49,323       5,514
      1-5-2021        704,111     50,692       5,514
      1-6-2021        707,722     52,182       5,514
      1-7-2021        710,665     53,268       5,514
      1-8-2021        712,798     53,906       5,514
      1-9-2021        714,859     54,489       5,514
      1-10-2021   
      1-11-2021   

       

      What I want is the calculate the difference between 1-12-2020 vs 1-1-2021, 1-2-2021 vs 1-3-2021, etc.. for Meter A, B, and C

      So I can make a bar chart which displays the usage per month per meter.

       

      Thanks,

      Erwin

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Greg_Deckler 

     

    Hi Greg,

     

    Thanks for your reply.

    in Excel i would perform the following steps to calculate the usage for meter A in January 2021;

    B3 - B2.

    The meters regard electricity. So with this calculation, I can see how much electricity has been used on meter A 

     

    I want to calculate this for meter a, b, c etc.. for each month.

     

     

     

    Thanks.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous OK, then you definitely want MTBF. But, I would recommend unpivoting your Meter columns first.