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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gkalandadze
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. 
Annotation 2019-12-11 153831.png
i'm attaching pbix file

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

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

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Thim
Resolver V
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.

dobregon
Impactful Individual
Impactful Individual

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.



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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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