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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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