March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
hi ,
i have a table like below. I want to calculate a measure base on current month. Lets say we are in june month now, so I want to calcuate a measure called '% expected' by using this logic
% cexpected = ( ( sum(Amount) where CalcType = sales ) - ( sum(Amount) where CalcType = sales expected ))/ ( sum(Amount) where CalcType = sales )
for the calculation only consider the months Jan to May because current month is June. If we are in next month July, i want this to be calculated for months 'Jan- June' , and so on dynamically. Can anyone suggest please.
@v-luwang-msft @Jihwan_Kim @parry2k @v-deddai1-msft @Greg_Deckler
Customer | Product | Fy | FiscalMonthYear | Month | CalcType | Amount |
Amazon | xyz | 2021 | 202101 | Jan | sales | 4000 |
Amazon | xyz | 2021 | 202102 | feb | sales | 1400 |
Amazon | xyz | 2021 | 202103 | Mar | sales | 850 |
Amazon | xyz | 2021 | 202104 | Apr | sales | 2100 |
Amazon | xyz | 2021 | 202105 | May | sales | 2300 |
Amazon | xyz | 2021 | 202106 | Jun | sales | 4270 |
Amazon | xyz | 2021 | 202107 | Jul | sales | 2320 |
Amazon | xyz | 2021 | 202108 | Aug | sales | 3220 |
Amazon | xyz | 2021 | 202109 | Sep | sales | 2100 |
Amazon | xyz | 2021 | 202110 | Oct | sales | 900 |
Amazon | xyz | 2021 | 202111 | Nov | sales | 5300 |
Amazon | xyz | 2021 | 202112 | Dec | sales | 7200 |
Amazon | xyz | 2021 | 202101 | Jan | sales expected | 6500 |
Amazon | xyz | 2021 | 202102 | feb | sales expected | 200 |
Amazon | xyz | 2021 | 202103 | Mar | sales expected | 3000 |
Amazon | xyz | 2021 | 202104 | Apr | sales expected | 2800 |
Amazon | xyz | 2021 | 202105 | May | sales expected | 1200 |
Amazon | xyz | 2021 | 202106 | Jun | sales expected | 2500 |
Amazon | xyz | 2021 | 202107 | Jul | sales expected | 850 |
Amazon | xyz | 2021 | 202108 | Aug | sales expected | 1500 |
Amazon | xyz | 2021 | 202109 | Sep | sales expected | 4000 |
Amazon | xyz | 2021 | 202110 | Oct | sales expected | 2300 |
Amazon | xyz | 2021 | 202111 | Nov | sales expected | 8500 |
Amazon | xyz | 2021 | 202112 | Dec | sales expected | 6500 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @srk_powerbi ,
Based on your sample data, you can create the following measure. My measure is updated dynamically, no slicer selection is required.
% cexpected =
var _aa=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[CalcType]="sales"&&[Fy]<=YEAR(TODAY())&&[Fm]<=MONTH(TODAY())))
var _bb=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[CalcType]="sales expected"&&[Fy]<=YEAR(TODAY())&&[Fm]<=MONTH(TODAY())))
return DIVIDE(_aa-_bb,_aa)
Tips: [Fm] column is a calculated column which retruns the month number.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @srk_powerbi ,
Based on your sample data, you can create the following measure. My measure is updated dynamically, no slicer selection is required.
% cexpected =
var _aa=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[CalcType]="sales"&&[Fy]<=YEAR(TODAY())&&[Fm]<=MONTH(TODAY())))
var _bb=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[CalcType]="sales expected"&&[Fy]<=YEAR(TODAY())&&[Fm]<=MONTH(TODAY())))
return DIVIDE(_aa-_bb,_aa)
Tips: [Fm] column is a calculated column which retruns the month number.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@srk_powerbi although @Ashish_Mathur solution will work in that case you have to select a period. if your ask is to always calculate % until previous month without selecting a month, add this measure (it is based on the file that @Ashish_Mathur shared in his response)
% Expected until Last Month =
VAR __date = EOMONTH ( TODAY(), -1 )
RETURN
CALCULATE ( DIVIDE ( [Numerator], [Sales YTD] ), 'Calendar'[Date] <= __date )
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
Hi,
You may download my PBI file from here.
Hope this helps.
@srk_powerbi , In case you have date or can create a date then you can use time intelligence with date table
example
calculate([% cexpected], datesmtd('Date'[Date])
else create a separate table with year month (say date)
add rank column
Month Rank = RANKX(all('Date'),'Date'[year Month],,ASC,Dense)
create measures
This Month = CALCULATE([% cexpected], FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE([% cexpected], FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
thanks for reply. here I need to calculate for all the months prior to current month dynamically.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
91 | |
79 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
72 | |
55 |