Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
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
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
65 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |