calculate measure help..

[srk_powerbi]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-stephen-msft · Accepted solution]

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.

[parry2k]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[srk_powerbi]

@v-luwang-msft 

[amitchandak]

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

[srk_powerbi]

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