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

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

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      Hey,

       

      open a new post please, and also describe your model, do you have a separate calendar table, the structure of your main table ad if possible provide easy to use sample data.

       

      Regards

  • AmalrajRRD1's avatar
    AmalrajRRD1
    Helper II
    9 years ago

    How to do the subtraction .

     

    can you help me on this

     

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      The subtraction can look like this

       

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

       

      Cheers

      • ContabilidadBI's avatar
        ContabilidadBI
        Helper III
        9 years ago

        Hi TomMartens,

         

        I created a new post here: http://community.powerbi.com/t5/Desktop/Active-Clients/m-p/229032#M102144

        AmalrajRRD1,

         

        Could you solve your problem?

        as Tom says you need the two measures and then substract them. Example:

        Sales = CALCULATE(SUM(Sales[SalesAmount]))
        SalesPreviousMonth = CALCULATE (SUM(Sales[SalesAmount]), PREVIOUSMONTH(Sales[SalesAmount])

        Diferrence= [Sales] - [SalesPreviousMonth]

        If you use the difference measure in a table with months of the rows for example, each month you will see the difference bteween the sales of that month and the sales of the previous month.

        Hope that helps