Forum Discussion

AmalrajRRD1's avatar
AmalrajRRD1
Helper II
9 years ago
Solved

DAX

Hi all,   How to do Privous month calculation like i have current month (100)-privous month (50) = 50 how to do in dax..    Thanks
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    do you have a separate Calendar table, if not you should ;-) And make sure both tables are related properly.

     

    Then you should give PREVIOUSMONTH a try like so:

    CALCULATE(SUM('yourtable'[yournumericcolumn]), PREVIOUSMONTH('calendar'[Date]))

    Here you will find almost everything about time intelligence with DAX:

    http://www.daxpatterns.com/time-patterns/

     

    If you are new to the sometimes mind-boggling DAX statements I would wholeheartedly recommend the following books

     also in this sequence

    1. Analyzing Data with Microsoft Power BI and Power Pivot for Excel
    2. Dax Patterns 2015
    3. The Definitive Guide to DAX

    All three have been written by Marco Russo and Alberto Ferrari

     

    Hope this gets you started 

  • TomMartens's avatar
    TomMartens
    9 years ago

    The subtraction can look like this

     

    CALCULATE(SUM('yourtable'[yournumericcolumn])) - CALCULATE(SUM('yourtable'[yournumericcolumn],PREVIOUSMONTH('calendartable'[date]))

     

    Cheers

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

do you have a separate Calendar table, if not you should ;-) And make sure both tables are related properly.

 

Then you should give PREVIOUSMONTH a try like so:

CALCULATE(SUM('yourtable'[yournumericcolumn]), PREVIOUSMONTH('calendar'[Date]))

Here you will find almost everything about time intelligence with DAX:

http://www.daxpatterns.com/time-patterns/

 

If you are new to the sometimes mind-boggling DAX statements I would wholeheartedly recommend the following books

 also in this sequence

  1. Analyzing Data with Microsoft Power BI and Power Pivot for Excel
  2. Dax Patterns 2015
  3. The Definitive Guide to DAX

All three have been written by Marco Russo and Alberto Ferrari

 

Hope this gets you started 

  • AmalrajRRD1's avatar
    AmalrajRRD1
    Helper II
    9 years ago

    here i can see the differences right?

     

    Thanks

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      No, my example just gets you to calculate the value for the previous month, finally you have to do the subtraction :-)

      • ContabilidadBI's avatar
        ContabilidadBI
        Helper III
        9 years ago

        Hi TomMartens and others,

         

        I have a simple doubt about time with DAX and I was about to create a new topic but thought that it was a better idea to ask in a related post.

         

        I want to achieve this:

        ActiveClients = CALCULATE(DISTINCTCOUNT (Sales[ClientKey]); ---BETWEEN TODAY AND 3 MONTHS AGO---)             so I want to considerate as Active clients, clients who bought something in the last 90 days for example. So if they bought something before that (and didn't buy since then), they are not active clients. I guess I need to use MONTH(TODAY) but I was trying different formulas and could not get what I wanted. Any idea how can I do that? 

         

        Thanks!!!!!!!!