> For the complete documentation index, see [llms.txt](https://kb.bevica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kb.bevica.com/documentation/reporting-and-analytics/bevica_power_bi/bevica_power_bi_creating_example_reports.md).
# Bevica Power BI: Creating Example Reports
Worked examples for building common Power BI reports on Bevica data.
## Summary
This article is designed to give some practical examples of how to create a few common reports. By following these examples you should be able to start making best use of the data model for your specific needs.
Where fields are specified the following convention is used:
* Table > \[Folder] > Field
* e.g., Items > Item Classification > Brand Name
## Inventory Valuation
One of the limitations of the standard Inventory Valuation report in Business Central is that you cannot see which location the inventory is in without running the report multiple times.
### Relevant Documentation
You should have read and understood these articles:
* [Data Model - Core (Bevica Power BI)](/documentation/reporting-and-analytics/bevica_power_bi/data_model_-_core_-bevica_power_bi.md) (especially around inventory valuation)
* [Inventory Costings (Bevica BI)](/documentation/reporting-and-analytics/bevica_power_bi/inventory_costings_-bevica_bi.md)
* [Time Intelligence (Bevica Power BI)](/documentation/reporting-and-analytics/bevica_power_bi/dates_and_time_intelligence_-bevica_power_bi.md)
### Key Concepts
You need to make use of the following features:
* Applying a date filter to the report so the time intelligence measures work. Using a relative date filter is a good option as it can be dynamic (i.e., update as the date changes) but you may also opt for a hard date filter.
* Cumulative Total from time intelligence so that we're aggregating values rather than just looking at them for the selected date period above.
### Creating the Report
#### Data
Using a matrix visual add:
1. Rows = Items > Item ID so you have a list of items. You could also use Item Group (in the Item Classification folder) if you wanted to group items together (e.g., non-vintage)
2. Columns = Location > Location Code so it is split by Location. Depending on the data in the report and number of locations etc. you might want to use Location as a report level filter.
3. Values = Item Ledger Entries > Inventory Measures >
1. Inventory Quantity (Base) to show bottles (you could use Case or Eq Case too)
2. Inventory Cost (Expected) (see Inventory Costings measures article linked above)
3. Inventory Cost (Actual)
4. Inventory Cost (Non-Inventoriable)
5. Inventory Cost (All) - to show the sum of all three. Alternatively you may want to exclude Non-Inventoriable Costs in which case use Inventory Cost (Inventory)
#### Filters
We now need to add some filters to get the data right. If we add them at the page level then any visuals on this page will use these filters too - so we end up with consistent information.
**Date Filter**
1. Add Dates > Date to the 'Filters on this Page' filter ([filters](https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter?tabs=powerbi-desktop#levels-of-filters-in-the-filters-pane))
2. Select the Filter type to be 'Relative Date' ([relative dates](https://learn.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range))
3. Select 'is in this' and 'day' to make it always be filtering to 'today'
This applies a filter then to only today. This will immediately restrict the entries to just 'today' which means the inventory measures are not going to be correct.
**Cumulative Total**
So we need to update the calculation to take values from the beginning of time (in terms of Business Central!) to the date filter applied above. To do this we'll manipulate the calculations using time intelligence functions.
1. Add Time Intelligence > Time Period to the page filters
2. Select the 'Cumulative Total' value
This means that the inventory calculations are now summing up all values from the first entry to the date we set above (so e.g., from 01/01/2022 to 30/10/2023) rather than just for the date above (i.e., 30/10/2023).
Your filters should look like this (your date should be different):
Your report layout should look similar to this (note the Location has been added as a filter at the top in this example:
## Customer Value
Sometimes you want to know the Customer value for this year and compare it to last year. This could also apply to Items, or perhaps against an attribute of the customer (like a posting group or price group). In this example we want to see "Lifetime Value" and "Last 12 month value".
### Key Concepts
You need to make use of the following features:
* Applying a date filter to the report so the time intelligence measures work. Using a relative date filter is a good option as it can be dynamic (i.e., update as the date changes) but you may also opt for a hard date filter.
* Time intelligence for Cumulative Total and MAT 365
### Creating the Report
#### Data
Using a matrix visual add:
* Rows
* Salesperson Code (from the Customer record). In this case we're going to use the current Salesperson (i.e., who is currently responsible) but you could also use the Dimension Set Entries Salesperson Dimension Code if you wanted to see the transactional salesperson (i.e., who sold it at the time).
* Customer ID from the Customer
* Values
* Sales, Invoiced (ILE)
* Columns
* It's best to prepare this first so you don't get a lot of additional data so...
#### Column Preparation
* First we want to **limit the dates** of transactions so we are calculating just based on today. Add a page level filter of Dates\[Date] and set it to a Filter Type = Relative Date and "is in this" and "day". This means it's dynamic (tomorrow will be a new day and the date will move along) rather than setting a hard filter of e.g., 14/11/2023.
* You can also use the 'Date has ILE Posting' to achieve a similar result - this will make sure that the date table is always filtered by dates up to the latest item ledger posting date. The difference here is that if your report is run on a Monday (assuming you're closed at the weekend!) then the "is in this" and "day" filter will be Mondays date (so will include 2 days of no transactions ) whereas the 'Date has ILE Posting' would filter to the Friday (assuming that had posted entries!) so would be more accurate.
* You might also consider using only full months so perhaps use the Relative Date "is in the last" "1" "calendar months" which would be up to the end of last month
* Next we want to only use the specific **Time Intelligence** values we're interested in. For this one I want:
* Cumulative Total - their "lifetime value"
* MAT (365) - their value in the last 12 months
* MAT LY (365) - their value in the prior 12 months to that
* MAT vs. LY % - how the above two have changed
* So add the Time Period filter to the Filters on this Visual (or page, depending on whether you'll add more charts etc.) and select the above:
#### Columns (again)
Now we've prepared the data add the **Time Period** to the columns. Because of the filter applied above it will only show the values we've selected.
#### Result
So now you can see total lifetime value of your customers (and salespeople). You can use 'Show Value As' on the Values field to e.g., show the values as 'Percent of Column Total' if you want to see the share of a customer in that column. Or use another visual alongside it to graphically represent it.
 
> You can easily swap out the Sales measure for say Margin to view contribution.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://kb.bevica.com/documentation/reporting-and-analytics/bevica_power_bi/bevica_power_bi_creating_example_reports.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
