# How to build profit & loss report with additional data import

**URL:** <https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71>\
**Category:** Tips & Tricks\
**Tags:** data-mapping, dataimport, mdx\
**Created:** [June 4, 2017, 11:57am UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71 "2017-06-04T11:57:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![daina.tupule](https://community.eazybi.com/user_avatar/community.eazybi.com/daina.tupule/32/8838_2.png) [@daina.tupule](https://community.eazybi.com/u/daina.tupule)\
**Post date:** [June 4, 2017, 11:57am UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/1 "2017-06-04T11:57:55Z")

</div>

## Overview

In this example, JIRA has custom field Cost Center and a file with a budget for each Cost Center per month. Cost calculation goes over logged hours and user rate. Users have flat rates in a seperate file.

The example describes how to import both files into eazyBI, mapping budget to custom field Cost Center and Time, and mapping Rate to Logged by User. You could either upload a file, import data via REST API, or SQL, or use Google Sheets in the same way. eazyBI supports additional data import to any existing default field dimension and single select custom field dimensions as properties or measures.

## Budget import

A budget should be imported and mapped to custom field Cost Center and Time. A budget should be imported as Measure to enable mapping by several dimensions. eazyBI by default does not allow additional data import to custom fields. This option should be enabled for a custom field that requires additional data import.

JIRA administrator can enable additional data import in [eazyBI advanced settings](https://docs.eazybi.com/display/EAZYBIJIRA/Advanced+settings+for+custom+fields):

```
[jira.customfield_NNNNN]
data_type = "string"
dimension = true
separate_table = true

```

Custom field ID should be used instead of NNNNN. Run an import after custom field definition changes.

Prepare a data source with budget data, over time and the custom field values. You could add any other dimensions there as well. Please split data as detailed as you would need for data analysis later. Upload file or setup connection and query for any other data sources. Then proceed to column mapping.

Select JIRA issue cube for additional data import. You would like to import budget as a measure mapped to two dimensions - custom field dimension and time dimension. Select dimension and level from selections list. Set advanced options for mapping data via Name. Usually, you could map custom field value by Name. Set an option ‘Skip missing’ to ignore lines that do not have a value in the eazyBI. eazyBI does not import lines without a proper match and could raise an error and prevent any data import if this parameter is not set.

 ![](https://community.eazybi.com/uploads/default/original/1X/6589afe0183deff590f15137c52eac27de1a3c82.png)  
Run an import.

## Rates import for a user

Cost calculation will be done over logged hours. eazyBI has several user dimensions and in this case Logged by dimension is the most appropriate for rate import to users who have logged hours.

Prepare a data source with usernames and rates. It would be suggested to use a username for mapping as several users could have the same name and surname. Username is unique and would suit better for additional data import. Upload file or setup connection and query for any other data sources. Then proceed to column mapping.

Select JIRA issue cube for additional data import. You would like to import rates as a property mapped to Logged by user on user level. You can import only one property value for each user. Any new property value will override the previous one for the same user.

Select dimension and level from selections list. Set advanced options for mapping data via Key (this selection goes with username). Set an option ‘Skip missing’ to ignore lines that do not match Logged by user in the eazyBI. eazyBI does not import lines without a proper match and could raise an error and prevent any data import if this parameter is not set.

 ![](https://community.eazybi.com/uploads/default/original/1X/9829bd040e8b1ccd9ec53f670286179082620c4f.png)  
Run an import.

## Create a report

A report uses profit/loss calculation subtracting Cost from Budget cumulatively over time to get a value at the end of each period.

Create a calculated measure for “Cost”:

```
NonZero(SUM(Filter(
  Descendants([Logged by].CurrentMember, [Logged by].[User]),
  [Measures].[Hours spent] > 0),
  [Measures].[Logged by Rate] *
  [Measures].[Hours spent]
))

```

Create a calculated measure “Cost Center Budget” to get it working in any context in the report:

```
DefaultContext(
([Measures].[Budget],
[Cost center].CurrentMember,
[Time].CurrentHierarchyMember)
)

```

Create a calculated measure “Profit/Loss Cumulative” for cumulative profit/loss calculation:

```
NonZero(SUM(
  {PreviousPeriods([Time].CurrentHierarchyMember),
  [Time].CurrentHierarchyMember},
  [Measures].[Cost Center Budget]
  -
  [Measures].[Cost]
))

```

Create a report using measure “Profit/Loss Cumulative” on Columns. Use Coste center with Cost center level on Columns as well. Use Time dimension with Month level selected on Rows. Switch your report to Timeline chart. You could also use Bar chart as well.

 ![](https://community.eazybi.com/uploads/default/original/1X/3e86ed244184a4dd8d24654110393fba777c283e.png)

---

<div class="post-metadata">

**Author:** ![pbyrne](https://community.eazybi.com/letter_avatar_proxy/v4/letter/p/919ad9/32.png) [@pbyrne](https://community.eazybi.com/u/pbyrne)\
**Post date:** [September 19, 2019, 7:16pm UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/2 "2019-09-19T19:16:53Z")

</div>

Hi, thanks for your excellent tutorial! Is there a way to accommodate a users rate changing over time? For example from the date of the users first ever work-log log, until the end of January 2019 the users rate was 60. Then, from the first of February 2019, they get a pay rise to 65, and then from 01 September 2019 they get another rise to 70…and there is potential for more increases in the future.

Can the report be somehow made aware of the users rate at and riven time period, and how would this be represented in the import data/Csv file format?

---

<div class="post-metadata">

**Author:** ![ronak\_kc](https://community.eazybi.com/letter_avatar_proxy/v4/letter/r/fbc32d/32.png) [@ronak\_kc](https://community.eazybi.com/u/ronak_kc)\
**Post date:** [May 3, 2022, 3:21pm UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/3 "2022-05-03T15:21:15Z")

</div>

Hi ,

Thank you for the detailed instructions. I was wondering if there’s a way to create new dimensions in the existing Issues cube and then import data from an Excel file into it?

---

<div class="post-metadata">

**Author:** ![Javier\_Nicolalde](https://community.eazybi.com/user_avatar/community.eazybi.com/javier_nicolalde/32/7243_2.png) [@Javier\_Nicolalde](https://community.eazybi.com/u/Javier_Nicolalde)\
**Post date:** [June 3, 2022, 4:18am UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/4 "2022-06-03T04:18:58Z")

</div>

Thank you for the example. However, I have a different problem to solve. I have a google sheet with (user, cost per Sprint). I would like to import the data from the sheet and calculate the cost per story point completed per user, every sprint. In other words, how much do I pay each developer per Story Point during a sprint? The cost per sprint per user will not change often during the year. Please help?  
thank you

---

<div class="post-metadata">

**Author:** ![daina.tupule](https://community.eazybi.com/user_avatar/community.eazybi.com/daina.tupule/32/8838_2.png) [@daina.tupule](https://community.eazybi.com/u/daina.tupule)\
**Post date:** [July 8, 2022, 10:12am UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/5 "2022-07-08T10:12:56Z")

</div>

eazyBI allows creating new dimensions in the issue cube with [calculated JavaScript custom fields](https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/javascript-calculated-custom-fields). If they represent a single value per an individual issue you can import additional similar to how it is described for Cost center.

Daina / [support@eazybi.com](mailto:support@eazybi.com)

---

<div class="post-metadata">

**Author:** ![daina.tupule](https://community.eazybi.com/user_avatar/community.eazybi.com/daina.tupule/32/8838_2.png) [@daina.tupule](https://community.eazybi.com/u/daina.tupule)\
**Post date:** [July 8, 2022, 10:40am UTC](https://community.eazybi.com/t/how-to-build-profit-loss-report-with-additional-data-import/71/6 "2022-07-08T10:40:22Z")

</div>

You can use a similar approach - import Rate as property per user and use the same calculations as represented by Rate. However, this will not support cases when the cost per sprint changes.

The solution will work for the latest sprints only with this approach. In some cases, it might be enough to get the needed values for the most recent sprints and get the costs per sprint. It won’t allow you to get back in history, though.

If you would like to keep track of costs per each individual sprint the solution will require more maintenance. You would like to import the rate per each user and per each sprint as a measure. You would need to have this mapping for any sprint you would like to calculate the costs. The solution will be more close to the one on how the Cost is imported and used in the formulas. However, you would like to use the user (Logged by or Assignee) dimension and the Sprint dimension instead of Cost center and Time.

You can prepare the file using the report with Sprint dimension and User dimension on Rows. See more here on how to use reports for preparing the import file:

> [@Import properties for Jira Assignees and build custom hierarchy](https://community.eazybi.com/t/import-properties-for-jira-assignees-and-build-custom-hierarchy/8624):
>
> eazyBI allows [importing additional properties](https://docs.eazybi.com/eazybi/data-import/data-from-jira/additional-data-import-into-jira-issues-cube) for dimensions and [creating custom hierarchies](https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-AddcustomhierarchiestospecificJira,Confluence,andInsightdimensions). In this example, I will walk you through how to define and import property Department for Assignee and build a custom hierarchy in the dimension Assignee. You can use the same steps to add more details for assignees or any other user dimension. Create a list of users for your source data. You can use eazyBI as a basis to create your data source. Create a new empty report in eazyBI. Use Transition Auth…

 ![image](https://community.eazybi.com/uploads/default/original/2X/d/df0efe95393626d33264103c5f0d84e95c821d85.png)

Export the file and set the cost per sprint and user, even if it is the same cost for different sprints.

 ![image](https://community.eazybi.com/uploads/default/original/2X/e/e6f905a5f62581791190cc8a18c53e828b365945.png)

I removed the columns sprint and assignee from my file. Then make the mapping - by Sprint and User dimension based on Key and Skip missing, set the Measure for the rate:

 ![image](https://community.eazybi.com/uploads/default/original/2X/c/c9c641002f81d7d9bfb78d012556a6dd583b8b2a.png)

Then it will be imported and you can use them in the calculations:

 ![image](https://community.eazybi.com/uploads/default/original/2X/4/47bdb0e7a0c08400fca2af729d1120740d74234b.png)

Please do not use the calculation directly, apply the formula and then use this formula in reports with other dimensions:

```auto
DefaultContext(
([Measures].[User rate per sprint],
[Assignee].CurrentHierarchyMember,
[Sprint].CurrentHierarchyMember)
)

```

Daina / [support@eazybi.com](mailto:support@eazybi.com)