Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all together,
I would be glad if someone could give me the solution. I have read through many many post, but did not find what I was looking for.
I have a table like this and want to calculate the monthly sales by month and business unit.
Date | Business Unit | Sales cumm. | monthly sales |
31.01.2016 | ABC | 388.286 | 388.286 |
28.02.2016 | ABC | 1.128.132 | 739.846 |
31.03.2016 | ABC | 1.742.896 | 614.764 |
30.04.2016 | ABC | 2.196.541 | 453.645 |
31.05.2016 | ABC | 2.654.779 | 458.238 |
30.06.2016 | ABC | 3.295.551 | 640.772 |
31.07.2016 | ABC | 3.847.267 | 551.716 |
31.08.2016 | ABC | 4.210.714 | 363.447 |
30.09.2016 | ABC | 4.749.356 | 538.642 |
31.10.2016 | ABC | 5.351.004 | 601.647 |
30.11.2016 | ABC | 5.808.109 | 457.106 |
31.01.2016 | DEF | 436.728 | 436.728 |
28.02.2016 | DEF | 924.156 | 487.428 |
31.03.2016 | DEF | 1.512.040 | 587.884 |
30.04.2016 | DEF | 2.173.243 | 661.202 |
31.05.2016 | DEF | 2.629.142 | 455.899 |
30.06.2016 | DEF | 3.036.177 | 407.035 |
31.07.2016 | DEF | 3.774.962 | 738.785 |
31.08.2016 | DEF | 4.340.396 | 565.434 |
30.09.2016 | DEF | 4.885.096 | 544.700 |
31.10.2016 | DEF | 5.796.794 | 911.698 |
30.11.2016 | DEF | 6.598.860 | 802.066 |
I guess for the most of you it´s quite simple, but at least I have no idea.
Thank you very much!
Solved! Go to Solution.
Hi @ChrisZ
The solution is to find the cumulated valued upto the previous month and use this value to subtract from current month cumulated value to derive the months value.
The data supplied by you is loaded as table name CumSales. The date column of this table should be set to dd/mm/yyyy format.
Here you go.
Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))
Set the column Date to format dd/mm/yyyyy.
Month = Month([Date])
MonthDayNumber = DAY(Calendar[Date])
MonthDays = COUNTROWS ( FILTER( 'Calendar', 'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )
Year = YEAR('Calendar'[Date])
YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1
PMDate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
ALL ( 'Calendar' ),
FILTER (
ALL ( 'Calendar'[MonthDayNumber] ),
'Calendar'[MonthDayNumber] <= EARLIER ( 'Calendar'[MonthDayNumber] )
|| EARLIER('Calendar'[MonthDayNumber] ) = EARLIER ( 'Calendar'[MonthDays] )
),
FILTER (
ALL ( 'Calendar'[YearMonthNumber] ),
'Calendar'[YearMonthNumber]
= EARLIER ( 'Calendar'[YearMonthNumber] ) - 1
) )
I have used monthly sales from your sample data as the **bleep** Sales contains decimal numbers instead of comma and had no time to alter.
For your testing replace this as SalesCum = Sum(CumSales[Sales cumm.])
PMTD Sales = CALCULATE (
[SalesCum],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1
&& 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )
)
)
MonthsValue = [SalesCum]-[PMTD Sales]
MonthsValue.
If this works for you please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Sample screen shot
Hi @ChrisZ,
According to your description, you want to group the data by date and business unit, right?
If this is a case, you can refer to below methods to achieve your requirement.
1. Group with matrix visual.
2. Group with summarize function.
Table = SUMMARIZE(Sheet1,[Format].[MonthNo],[Business Unit],"Sales cumm",SUM(Sheet1[Sales cumm.]),"Monthly Sales",SUM(Sheet1[monthly sales]))
Result:
Regards,
Xiaoxin Sheng
Hello @v-shex-msft,
what I need is a DAX Formula / Measure to calculate the monthly sales by Business unit, if I have only the following data table.
Date | Business Unit | Sales YTD |
31.01.2016 | ABC | 388.286,40 |
28.02.2016 | ABC | 1.128.132,20 |
31.03.2016 | ABC | 1.742.896,10 |
30.04.2016 | ABC | 2.196.540,98 |
31.05.2016 | ABC | 2.654.778,87 |
30.06.2016 | ABC | 3.295.550,83 |
31.07.2016 | ABC | 3.847.266,86 |
31.08.2016 | ABC | 4.210.714,27 |
30.09.2016 | ABC | 4.749.356,21 |
31.10.2016 | ABC | 5.351.003,68 |
30.11.2016 | ABC | 5.808.109,29 |
31.01.2016 | DEF | 436.728,00 |
28.02.2016 | DEF | 924.156,41 |
31.03.2016 | DEF | 1.512.040,40 |
30.04.2016 | DEF | 2.173.242,52 |
31.05.2016 | DEF | 2.629.141,96 |
30.06.2016 | DEF | 3.036.177,26 |
31.07.2016 | DEF | 3.774.962,39 |
31.08.2016 | DEF | 4.340.396,10 |
30.09.2016 | DEF | 4.885.096,11 |
31.10.2016 | DEF | 5.796.794,08 |
30.11.2016 | DEF | 6.598.859,98
|
So i.e. the result of this formula should be for ABC in January is 388.286,40; for February it´s 739.845,80 etc.
I hope it is a little bit more understandable. now.
Thanks again!
Christian
Hi @ChrisZ
The solution is to find the cumulated valued upto the previous month and use this value to subtract from current month cumulated value to derive the months value.
The data supplied by you is loaded as table name CumSales. The date column of this table should be set to dd/mm/yyyy format.
Here you go.
Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))
Set the column Date to format dd/mm/yyyyy.
Month = Month([Date])
MonthDayNumber = DAY(Calendar[Date])
MonthDays = COUNTROWS ( FILTER( 'Calendar', 'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )
Year = YEAR('Calendar'[Date])
YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1
PMDate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
ALL ( 'Calendar' ),
FILTER (
ALL ( 'Calendar'[MonthDayNumber] ),
'Calendar'[MonthDayNumber] <= EARLIER ( 'Calendar'[MonthDayNumber] )
|| EARLIER('Calendar'[MonthDayNumber] ) = EARLIER ( 'Calendar'[MonthDays] )
),
FILTER (
ALL ( 'Calendar'[YearMonthNumber] ),
'Calendar'[YearMonthNumber]
= EARLIER ( 'Calendar'[YearMonthNumber] ) - 1
) )
I have used monthly sales from your sample data as the **bleep** Sales contains decimal numbers instead of comma and had no time to alter.
For your testing replace this as SalesCum = Sum(CumSales[Sales cumm.])
PMTD Sales = CALCULATE (
[SalesCum],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1
&& 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )
)
)
MonthsValue = [SalesCum]-[PMTD Sales]
MonthsValue.
If this works for you please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Sample screen shot
Hi @ChrisZ
Simply create a measure called SumSales = sum([Monthly_sales])
Create slicers for business unit .
Create slicers for Date.Month
Plot any graph with SumSales measure as values and Business unit as Axis.
SImilarly plot any graph with SumSales measure as values and Date as Axis.
You will achieve what you want.
If this works for you please accept this as solution and also give KUDOS.
Cheers
CheenuSing
Hi @CheenuSing,
thank you for your quick reply. I guess my question/description was not precise enough.
I only have in my data table the following columns: Date, BU, Sales YTD per BU
Now I want to create a measure or additonal column in Power BI that calculates the monthly sales of the "corresponding" BU.
The 4th column in my table only shows, what I expect as result of the measure, but is it not existing yet.
Would be great if you can help on this aswell.
Christian
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |