topic Re: Help with Calculation - 1 Month Sales over 12 Months in a Chart in DAX Commands and Tips

Help with Calculation - 1 Month Sales over 12 Months in a Chart

aweger — Mon, 11 Dec 2023 18:04:38 GMT

Hi everyone - I am extremely new to BI and could use some help.

My question is this:

Lets say I have an account that has an sale set to close in January for an estimated revenue. But that revenue is set to be paid over 12 months - what is the best way to model that in BI?

So scenario is

Jims TV's is set to close a 12 Million dollar deal in Janaury, but that payment will be realized over 12 months. Currently my data says

Jims TV's 12M for January

But I want to set it up so it shows on a chart

Jims TV's

January 1M, February 1M, March 1M, etc....over 12 months in the future.

Does this make sense? We have thousands of accounts so I need to set a chart where I can filter and show by region for example how the revenue is phased month by month over a year

Re: Help with Calculation - 1 Month Sales over 12 Months in a Chart

123abc — Tue, 12 Dec 2023 05:20:11 GMT

Here's a step-by-step guide:

Step 1: Create a Date Table

Power BI generally works well when you have a date table. If you don't already have a date table, you can create one. You can use the "Enter Data" option to manually input a date range that covers the period you are interested in. Alternatively, you can import a date table from your data source.

Step 2: Create a Revenue Table

Create a table with columns for Account Name, Deal Amount, and Close Date. For each account, enter the deal amount and the close date. It would be best to have one row for each account for each month that the revenue is allocated.

Step 3: Merge Date and Revenue Tables

Merge the Date table and the Revenue table based on the close date. This way, you'll have a single table with all the necessary information.

Step 4: Calculate Allocated Revenue

Create a new column in the merged table to calculate the allocated revenue for each month. You can use a formula similar to the one below:

DAX:

Allocated Revenue = [Deal Amount] / 12

Step 5: Create a Chart

Now, you can create a chart using the Account Name and the Allocated Revenue columns. You can use a line chart or an area chart to visualize the revenue over the months.

Step 6: Apply Filters

To allow filtering by region or other criteria, you can use slicers. Create slicers for the relevant columns (e.g., Region) and connect them to the charts.

Step 7: Explore in Power BI

Publish your report to the Power BI service to share it with others or to schedule data refreshes if your data is regularly updated.

Example DAX Measures

Here are some DAX measures you might find useful:

DAX:

Total Allocated Revenue = SUM('YourTable'[Allocated Revenue])

This measure gives you the total allocated revenue for the selected period.

DAX:

Monthly Allocated Revenue = SUMX(VALUES('Date'[Month]), [Allocated Revenue])

This measure gives you the monthly allocated revenue.

Adjust these measures based on your specific requirements and data model. I hope this helps!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Help with Calculation - 1 Month Sales over 12 Months in a Chart

123abc — Tue, 12 Dec 2023 05:20:13 GMT

Here's a step-by-step guide:

Step 1: Create a Date Table

Step 2: Create a Revenue Table

Step 3: Merge Date and Revenue Tables

Merge the Date table and the Revenue table based on the close date. This way, you'll have a single table with all the necessary information.

Step 4: Calculate Allocated Revenue

Create a new column in the merged table to calculate the allocated revenue for each month. You can use a formula similar to the one below:

DAX:

Allocated Revenue = [Deal Amount] / 12

Step 5: Create a Chart

Now, you can create a chart using the Account Name and the Allocated Revenue columns. You can use a line chart or an area chart to visualize the revenue over the months.

Step 6: Apply Filters

To allow filtering by region or other criteria, you can use slicers. Create slicers for the relevant columns (e.g., Region) and connect them to the charts.

Step 7: Explore in Power BI

Publish your report to the Power BI service to share it with others or to schedule data refreshes if your data is regularly updated.

Example DAX Measures

Here are some DAX measures you might find useful:

DAX:

Total Allocated Revenue = SUM('YourTable'[Allocated Revenue])

This measure gives you the total allocated revenue for the selected period.

DAX:

Monthly Allocated Revenue = SUMX(VALUES('Date'[Month]), [Allocated Revenue])

This measure gives you the monthly allocated revenue.

Adjust these measures based on your specific requirements and data model. I hope this helps!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.