Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

1 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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

@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 

erwinvandam_0-1632387377680.png

 

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

 

 

 

Thanks.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 

 

From this:

erwinvandam_0-1632388071037.png

 

To this?

erwinvandam_1-1632388111177.png

 

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

erwinvandam_0-1632394399593.png

 

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

erwinvandam_0-1632395354078.png

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 See Page 9 of attached PBIX below sig.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors