Month-over-Month calculation using DAX in Power BI

Question

Month-over-Month calculation using DAX in Power BI

aveek22

Mr or Mrs. 500

Points: 509
More actions
January 27, 2020 at 12:00 am

#3711988

Comments posted to this topic are about the item Month-over-Month calculation using DAX in Power BI
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

The problem with links on other sites is that other sites have been known to go away. Could you attach the .ZIP file to this site please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

Shifting gears to the subject matter... I don't see how anything led to answering the two questions posed at the beginning of the article, which were...

It can be used to answer questions like:
Has my customer conversion rate improved since this time last quarter?
What is my annual sales growth as compared to last year?

Can you provide a demonstration of how to create charts for those two questions using a similar technique as your previous month example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63502 More actions · Answer 3

Has my customer conversion rate improved since this time last quarter?

What is my annual sales growth as compared to last year?

The answers to the questions can be found by using time intelligence functions in DAX. Might be better if he created simple measures for the current evaluation context, and then modified that using CALCULATE() etc. Ferrari & Russo have a great website that covers this stuff: http://www.daxpatterns.com

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

pietlinden wrote:

Has my customer conversion rate improved since this time last quarter?
What is my annual sales growth as compared to last year?
The answers to the questions can be found by using time intelligence functions in DAX. Might be better if he created simple measures for the current evaluation context, and then modified that using CALCULATE() etc. Ferrari & Russo have a great website that covers this stuff: http://www.daxpatterns.com

Understood but it seems like, since the questions were brought up in this article, they should have been answered in this article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63502 More actions · Answer 5

I agree. Normally, in an article like that, the author would pose the questions and maybe format them as bullet points because that makes them stick out... then later he can refer back to the bullet point and answer the question.

But not answering the question at all is a big fail. If it's supposed to be a technical article, show the math/formulas.

Archana Old Hand Points: 332 More actions · Answer 6

Thanks, @jeff-moden, for pointing that out.

This reply was modified 4 years, 9 months ago by Archana. Reason: wrong comment

Ray Giacalone Grasshopper Points: 14 More actions · Answer 7

Hi thx for the article write up....appreciate it.

so my months are sorting alphabetically instead of by datetime

APR

AUG

DEC

FEB

ETC

D'Oh! Such a pain how do I fix?

pietlinden SSC Guru Points: 63502 More actions · Answer 8

Go into the Calendar/Dates table, and select the CalendarMonth column. Then in the ribbon, click on "Sort By Column" and select MonthNumber

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 9

pietlinden wrote:

I agree. Normally, in an article like that, the author would pose the questions and maybe format them as bullet points because that makes them stick out... then later he can refer back to the bullet point and answer the question.
But not answering the question at all is a big fail. If it's supposed to be a technical article, show the math/formulas.

I know that people get busy but it would also appear that the author isn't interested in this discussion or any of the questions posed about his article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 10

Thank you for your comment, @Ray-Giacalone, and @pietlinden.

If you have a MonthNumber field in your dataset, you should sort all your month-wise charts by that field. That way your month labels will be sorted according to the numbers and not alphabetically.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

pietlinden SSC Guru Points: 63502 More actions · Answer 11

Why not create a Total Sales measure, and then reuse it?

[Total Sales] := SUM(Sales[ExtendedAmount])

[PM Sales] := CALCULATE ( [Sales], DATEADD(DimDate[FullDateAlternateKey],-1,MONTH) )

Then growth is just

[Total Sales] - [PM Sales]

I just find starting simple to be much easier.

taklee Newbie Points: 1 More actions · Answer 12

the power bi file could not be downloaded. please attached again