cancel
Showing results for
Did you mean:
Frequent Visitor

## Actual VS Plan

Hello , could anyone help me with this. i'm new and can't solve this.

i got 2 data

1. plan with monthly sales plan
2. and sales by days.

i want to aggregate days sales sum and compare with month plan.

i'm attaching pbix file

https://1drv.ms/u/s!Aucu3BmZNQw-p6ZB8wCVtOstsLB1Bg?e=b9Hgv8

1 ACCEPTED SOLUTION
Community Support

You can try to use following calculate columns if they suitable for your requirements:

``````Actual sales =
CALCULATE (
SUM ( Sales[Income] ),
FILTER (
Sales,
[Code] = EARLIER ( Plan[Code] )
&& [Object] = EARLIER ( Plan[Object Store] )
&& MONTH ( [Date] )
= MONTH ( DATEVALUE ( EARLIER ( Plan[Month] ) & "/1" ) )
)
)

daily Sales =
VAR dt =
DATEVALUE ( Plan[Month] & "/1/" & YEAR ( TODAY () ) )
VAR duration =
DAY ( DATE ( YEAR ( dt ), MONTH ( dt ) + 1, 1 ) - 1 )
RETURN
Plan[Actual sales] / duration
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

You can try to use following calculate columns if they suitable for your requirements:

``````Actual sales =
CALCULATE (
SUM ( Sales[Income] ),
FILTER (
Sales,
[Code] = EARLIER ( Plan[Code] )
&& [Object] = EARLIER ( Plan[Object Store] )
&& MONTH ( [Date] )
= MONTH ( DATEVALUE ( EARLIER ( Plan[Month] ) & "/1" ) )
)
)

daily Sales =
VAR dt =
DATEVALUE ( Plan[Month] & "/1/" & YEAR ( TODAY () ) )
VAR duration =
DAY ( DATE ( YEAR ( dt ), MONTH ( dt ) + 1, 1 ) - 1 )
RETURN
Plan[Actual sales] / duration
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Resolver V

I think you will need a few more tables to be able to make this.

I would make tables for the following new tables, and then make relations from your two original tables to these.

Object Store

Product Catagoru

Vode

Date

When the new tables are made, you can then make the matrix our of the values of the new tables, and then drag the budget and sales values from the original table.

Also i would add a date in your budget table as well, to make it easier to make the relation to the date table.

Also if you don't, the repport can't distinct if a month is from 2019 or 2020, so it will be wrong, if you plan to use the report for over a year..

I would either just use the first or the last of every month.

Impactful Individual

I imagen you have 2 tables loaded in the power BI:

1. Plan in monthly basis

2. Actual in daily basis

I suppose that you have a column to do a relationship, i imagine that the column is Code becasue it seems the itemid for each product.

And i suppose that you want to have the actual daily values moved to the monthly table. to do that, you need to expand month column in monthly basis with more detail with Year and month in numbers, and the same in the Actual table.

After that you can create the calculated column in the table monthlys that takes the values from actual table like this

``Actual Sales = CALCULATE ( SUM(TableActuals[ActualSales]), filter(TablePlan, TablePlan[code] = TableActuals[Code] && TablePlan[Year] = TableActuals[Year] && TablePlan[Month] = TableActuals[Month]))``

I'm not sure if it is this "filter(TablePlan)" or "filter(TableActuals)", try both options. This formula will paste the values for each code, grouping by year and month, so, the daily values will be sumarized by Code-Year-Month.

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors