cancel
Showing results 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.

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

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

Hi Greg,

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

From this:

To this?

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

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:

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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

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

Super User

@Anonymous See Page 9 of attached PBIX below sig.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors