Month-over-Month calculation using DAX in Power BI

  • 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/

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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

    • This reply was modified 10 months, 1 week ago by  Archana. Reason: wrong comment
  • 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?

     

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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/

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

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

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