Forum Discussion
Calculating Data by Date Range in Power BI
I'm very new to Power BI, but I do have a development background. I am trying to learn how to create a profitability report for our organiztion. This report needs to be dynamic enough to look back at data from Dynamics CRM and determine what the total income, profit, and profit margin are for an employee. I am importing data from Dynamics CRM through OData.
I have imported data for the last year into Power BI. I can find total income by month without any issue, but when it comes to calculating profit, I'm stuck trying to calculate by the selected date range. My calculation is basically Total Income - Total Employee Cost = Profit where Total Employee Cost = Salary + Bonus + Overhead. That cost divided by 12 gives me my monthly cost, but if a user is trying to report on 3 months, I'm having trouble figuring out how to take the Monthly Cost and multiply it by the number of months selected.
I may be completely off target but why not use the measures you already have and use your months as the axis or as rows to show the relevant data by specific month. Using a date calendar you could also show by calendar quarter. In other words, let your measures do the simple part of the equation (the math) and have PowerBI divide it up into periods in the visualizations using slicers and filters.
Of course it has been a long week and I may be completely misunderstanding the question.
8 Replies
- konstantinos
Memorable Member
Without knowing the exact formula or the granularity of the data is difficult to help you better, but you can try to divide the Total Income by a dynamic number of months like DIVIDE(Total Profit ; COUNTROWS(VALUES(Dates[Month]))) or something similar.
Close to that.
I have a measure for TotalIncome.
=SUM('Opportunities'[Fee])I have a measure for TotalEmployeeCost
='Employees'[MonthlyCost] * <insert code here for selected months >
I have a measure for TotalProfit
= [TotalIncome] - [TotalEmployeeCost]
TotalEmployeeCost is a fixed value so I need to multiply it by the number of selected months in the report view.
Hi, i am learner of power Bi how to calculate profit using dax expression
- kcantor
Community Champion
I may be completely off target but why not use the measures you already have and use your months as the axis or as rows to show the relevant data by specific month. Using a date calendar you could also show by calendar quarter. In other words, let your measures do the simple part of the equation (the math) and have PowerBI divide it up into periods in the visualizations using slicers and filters.
Of course it has been a long week and I may be completely misunderstanding the question.
Hello,
I've tried this approach of creating a measure using DATESBETWEEN Function, But this is not supported in DirectQuery mode.
I'm am presently working with DirectQuery mode. Do you have any alternative method for the above question?
Can someone please help me with this?
Thanks
Hi kcantor ,
The data is not matching until and unless the slicer end date is current date. But I need slicer end date as required and the data should match .
Can you please help on thisThanks in advance.
- ryans
Helper I
I know this is probably not super helpful but I believe what you need is to wrap your Income and Cost equations in a Calculate function and utilize a time intelligence function as the filter. You can find a listing of the time intelligence functions here: https://msdn.microsoft.com/en-us/library/ee634763.aspx.
