calculate current month vs previous month

[Anonymous]

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 date	Meter a	Meter b
1-7-2021	3652	4562
1-8-2021	3954	5021
1-9-2021	4014	5234

Could someone please help me with this (A)

Thanks in advance

[Anonymous]

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

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

[Anonymous]

@Greg_Deckler 

From this:

To this?

[Greg_Deckler · Accepted solution]

@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

[Anonymous]

@Greg_Deckler 

Thanks for the Syntax and taking time to help me out.

as you can see, an error occurs when I use the syntax. I don't know why...

[Greg_Deckler]

@Anonymous That's because I wrote it as a Column, not a Measure. Which is why I specified Column in the name. As a measure it would be:

Usage Measure = 
  VAR __CurrentValue = MAX([Value])
  VAR __Meter = MAX([Attribute])
  VAR __Date = MAX([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

[Greg_Deckler]

@Anonymous Oh well that's not going to work. Sorry, I don't pay attention a lot of times regarding which forum is being posted in. What I gave you is a DAX solution for a calculated column in the Desktop. You can't plug that into Power Query Editor because PQ uses M, not DAX.

[Anonymous]

@Greg_Deckler 

Ah oke. Good to know that the Query editor uses M-language and Dax is used within measures.

But when I try the syntax to do a measure, I also get an error:

[Greg_Deckler]

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

@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 reading	Meter A	Meter B	Meter 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

[Greg_Deckler]

@Anonymous See Page 9 of attached PBIX below sig.