Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
@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)
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
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
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |