Actual VS Plan

[gkalandadze]

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

[v-shex-msft · Accepted solution]

Hi @gkalandadze ,

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

[Thim]

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.

[dobregon]

I can't download the file but i can help you.

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.