cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

calculate measure help..

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.

 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
2 ACCEPTED SOLUTIONS
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

6 REPLIES 6
Community Support

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.

Super User

@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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II
Super User

@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)

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

Helper II

thanks for reply.  here I need to calculate for all the months prior to current month dynamically.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors