Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rajan_AN
Frequent Visitor

Forecasting issue in powerbi

I have a column in the dataset which tells me about the incremental revenue we are expected generate in the whole year, 
now i want to divide that by 12 and project it monthly, but i am unable to project it monthly for the cases where I dont have any information. 

example 

Rajan_AN_0-1713873525608.png

i dont have any entry for feb so it is showing zero in that but i want to show incremental revenue in feb as well 

1 REPLY 1
v-nuoc-msft
Community Support
Community Support

Hi @Rajan_AN 

 

To project the incremental revenue monthly in Power BI, including for months where you don’t have any data, you can use Power BI’s DAX formulas to create a calculated column or measure.

 

Ensure you have a date table that includes all the months of the year. If you don’t have one, you can create it using the DAX function calendar.

 

Date Table = CALENDAR("1/1/2024", "12/31/2024")

 

Make sure your date table is related to your main data table on the date column.

 

If your yearly incremental revenue is not already calculated, create a measure to sum up the total revenue for the year.

 

Use the following DAX formula to create a measure that divides the yearly revenue by 12 and assigns it to each month:

 

Monthly Incremental Revenue = 
DIVIDE(
    [Total Yearly Incremental Revenue],
    12,
    BLANK()
)

 

To handle months with no data, you can use a combination of and functions in DAX to check if there’s data for a month and, if not, to show the calculated monthly revenue.

 

Projected Monthly Revenue = 
IF(
    ISBLANK([Your Revenue Column]),
    [Monthly Incremental Revenue],
    [Your Revenue Column]
)

 

 Use this new measure to create your visualizations. 

 

Regards,

Nono Chen

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors