The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone,
I have this formula which calculate the sum of all my PO with type = "PO delivery" :
Solved! Go to Solution.
Hi @PBIBeginner2022 ,
I created some data:
Here are the steps you can follow:
1. Create measure.
sum of all month before the ongoing month =
CALCULATE(SUM('Table'[Delivered value in €]),FILTER(ALL('Table'),
'Table'[Date]>=DATE(
YEAR(TODAY()),1,1)&&
'Table'[Date]<
DATE(
YEAR(TODAY()),MONTH(TODAY()),1)))
sum after the ongoing month in one formula =
CALCULATE(SUM('Table'[Delivered value in €]),FILTER(ALL('Table'),
'Table'[Date]>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1&&
'Table'[Date]<=
DATE(
YEAR(TODAY())+1,1,1)-1
))
2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @PBIBeginner2022 ,
I created some data:
Here are the steps you can follow:
1. Create measure.
sum of all month before the ongoing month =
CALCULATE(SUM('Table'[Delivered value in €]),FILTER(ALL('Table'),
'Table'[Date]>=DATE(
YEAR(TODAY()),1,1)&&
'Table'[Date]<
DATE(
YEAR(TODAY()),MONTH(TODAY()),1)))
sum after the ongoing month in one formula =
CALCULATE(SUM('Table'[Delivered value in €]),FILTER(ALL('Table'),
'Table'[Date]>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1&&
'Table'[Date]<=
DATE(
YEAR(TODAY())+1,1,1)-1
))
2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I have a table date with a column MonthYear in format "MMMM YYYY" between "January 1899" to "December 2201". I need to calulate my sum with the specific type on all month of my column MonthYear except the current month -1. Could you help me please
Can you confirm whether the data type for that date column is date or string?
Hi @PBIBeginner2022 ,
Maybe something like this?
PO Delivery M-1 = VAR _ongoingMonth = MONTH ( TODAY() ) RETURN CALCULATE ( SUM (TPODeliveries[Delivered value in €]), TPODeliveries[Type] = "PO_Delivery"), MONTH ( TPODeliveries[Date] ) = _ongoingMonth )
You probably need to tweak some parts of the formular with the correct column names of your model etc. Also, I assumed the ongoing month is the same as the current month. I'd suggest to build a date table into your model, too, if you haven't done so.
Let me know if this helps 🙂 You may share some example data so it'll be easier to assist you, if things are still unclear.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
@tackytechtom thanks for your answer. But I don't want the sum of the current month. I want all month except the current month 😃
Have you a formula for this ? please
Hi @PBIBeginner2022 ,
Oh yeah! I misread.
Have a look in here:
Solved: Not Current Month of Current Year - Microsoft Power BI Community
Also, you might wanna consider to do the filtering on the visualisation level. The filter pane has a lot offer in that sense, too 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
Hi @PBIBeginner2022,
Have you tried adding the below filter condition in the calculate function?
table[date]<(EOMONTH(today(), -1) + 1)
If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
Atma.
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |