Forum Discussion
PBI Dax calculation
Hi Team,
I'm new to PBI,
I want to find out the profit variance in between the years, for example I want to find out 2013 profit variance.
So I have find out --->total profit : sum(profit)
---->2013Profit: calculate(total profit, order_date.date=2013)----> this is the original formula,
but when I trying to write 2013 profit formula, I'm not getting year date part("order_date.year"), so that I have to write a formula like
2013profit=calculate(total profit,year(order_date)=2013), but it was showing result same sa a total profit.
so please help me to resolve this. please see the below screenshot for your reference.
Thanks & Regards
Kumar.
You need to include a call to REMOVEFILTERS inside the CALCULATE statement. This should apply to the column in have grouped in rows in the visual. What column is that?
Supposing it is Sales[order_date],
CALCULATE (
[Total Profit],REMOVEFILTERS ( Sales[order_date] ),
YEAR ( Sales[order_date] )=2013
)PS you should use a calendar table and the removefilters would not be needed 🙂
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
5 Replies
You need to include a call to REMOVEFILTERS inside the CALCULATE statement. This should apply to the column in have grouped in rows in the visual. What column is that?
Supposing it is Sales[order_date],
CALCULATE (
[Total Profit],REMOVEFILTERS ( Sales[order_date] ),
YEAR ( Sales[order_date] )=2013
)PS you should use a calendar table and the removefilters would not be needed 🙂
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
If you don’t already have one, create a Date table, Mark is as Date Table and relate it to Sales Data[OrderDate].
Then your measure becomes simple and correct:
Profit YoY = [T.Profit] - CALCULATE ( [Total Profit], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Instead of hard-coding 2013, You should calculate previous year profit and variance:
Profit PY = CALCULATE( [Total Profit],
SAMEPERIODLASTYEAR(Calendar[Date]) )
Profit Variance = [Total Profit] - [Profit PY]
Hope that helps!Hi Kumardwhbidev,
Have you had a chance to review the solution we shared by FBergamaschi RicardoTraNa cengizhanarslan ? If the issue persists, feel free to reply so we can help further.
Thank you.
Hi Kumardwhbidev,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
