Forum Discussion

shanipowerbi's avatar
shanipowerbi
Icon for Helper III rankHelper III
6 years ago
Solved

Calculate Last month Amount

Hi

Hope you all are fine and healthy

 

I need very little help from you guys

 

I have two Columns

Date                    Amount         Last Month

01 Jan 2020         $ 5000

02 Feb 2020        $ 6000              $ 5000

03  March 2020   $ 7000             $ 6000

04 April 2020       $ 8000             $ 7000

 05 May 2020                              $ 8000

 

Here you can see when I use previous month dax it will give me April 2020 amount in May 2020 in Last Month Column, I want to stop that calculation till last month means future month not required.

 

Please help me to resolve this

 

Regards

 

Zeeshan

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi shanipowerbi ,

     

    Your method is right. You just need to add filter conditions to your formula.

    last_month = IF(MAX('table'[date].[Date]<TODAY(),CALCULATE(SUM('table'[amount]),DATEADD('table'[date].[Date],-1,MONTH)))

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi shanipowerbi ,

     

    Your method is right. You just need to add filter conditions to your formula.

    last_month = IF(MAX('table'[date].[Date]<TODAY(),CALCULATE(SUM('table'[amount]),DATEADD('table'[date].[Date],-1,MONTH)))

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    shanipowerbi change last month measure as below

     

    Last Month = 
<your existing measure>
* DIVIDE ( SUM ( Table[Amount] ), SUM ( Table[Amount] ) )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

     

    • shanipowerbi's avatar
      shanipowerbi
      Icon for Helper III rankHelper III
      6 years ago

      Not Worked,

      Let me Re-collaborate I am trying to get the previous month's Amount in Current month and I am successful to get this. But the problem I am facing that I am unable to stop this calculation further for future months.

       

      For Example I have two columns

      Date              Amount

      Jan                1000

      Feb               2000

      Mar               3000 

      April              500 (Which is not yet end)

       

      For the previous amount, I can use the formula CALCULATE(SUM('Invoice Data'[Amount),DATEADD('Invoice Data'[InvoiceDate].[Date],-1,MONTH))

      by Applying this formula I am getting this Answer

       

      Date             Amount               Previous Amount

      Jan               1000

      Feb               2000                     1000

      Mar              3000                      2000

      April             500                        3000

      May                                            500

       

      In the above result, I want data still March because its complete, but don't want April (Current Month) or any future month

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    shanipowerbi , You can try like

    LMTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

    • shanipowerbi's avatar
      shanipowerbi
      Icon for Helper III rankHelper III
      6 years ago

      Not Worked,

       

      Let me Re-collaborate

      I am trying to get the previous month's Amount in Current month and I am successful to get this. But the problem I am facing that I am unable to stop this calculation further for future months.

      For Example 

      I have two columns 

       

      Date            Amount

      Jan              1000

      Feb              2000

      Mar             3000

      April            500 (Which is not yet end)

       

      For the previous amount, I can use the formula 

      CALCULATE(SUM('Invoice Data'[Amount),DATEADD('Invoice Data'[InvoiceDate].[Date],-1,MONTH))
      by Applying this formula I am getting this Answer
       

      Date            Amount                Previous Amount       

      Jan              1000

      Feb              2000                   1000

      Mar             3000                    2000

      April            500                     3000

      May                                        500

       

      In the above result, I want data still March because its complete, but don't want April (Current Month) or any future month