cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChrisZ Frequent Visitor
Frequent Visitor

Calculation of monthly figures from YTD for serveral groups

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.

 

DateBusiness UnitSales cumm.monthly sales
31.01.2016ABC388.286388.286
28.02.2016ABC1.128.132739.846
31.03.2016ABC1.742.896614.764
30.04.2016ABC2.196.541453.645
31.05.2016ABC2.654.779458.238
30.06.2016ABC3.295.551640.772
31.07.2016ABC3.847.267551.716
31.08.2016ABC4.210.714363.447
30.09.2016ABC4.749.356538.642
31.10.2016ABC5.351.004601.647
30.11.2016ABC5.808.109457.106
31.01.2016DEF436.728436.728
28.02.2016DEF924.156487.428
31.03.2016DEF1.512.040587.884
30.04.2016DEF2.173.243661.202
31.05.2016DEF2.629.142455.899
30.06.2016DEF3.036.177407.035
31.07.2016DEF3.774.962738.785
31.08.2016DEF4.340.396565.434
30.09.2016DEF4.885.096544.700
31.10.2016DEF5.796.794911.698
30.11.2016DEF6.598.860802.066

 

I guess for the most of you it´s quite simple, but at least I have no idea.

Thank you very much!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculation of monthly figures from YTD for serveral groups

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.

  1. Create a calendar table using

Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))

Set the column Date to format dd/mm/yyyyy.

  1. Create column month in the calendar table

Month = Month([Date])

  1. Create column in calendar table

MonthDayNumber = DAY(Calendar[Date])

  1. Create column in calendar table

MonthDays = COUNTROWS ( FILTER( 'Calendar',  'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )

  1. Create column in calendar table

Year = YEAR('Calendar'[Date])

  1. Create column in calendar table

YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1

  1. Create column in calendar table

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

    )  )

    

  1. Define relationship between CumSales[Date] and Calendar[Date] columns.
  2. Create measure SalesCum = Sum(CumSales[monthly sales])

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.])

  1. Create measure PMTDSales

PMTD Sales = CALCULATE (

    [SalesCum],

    FILTER (

        ALL ( 'Calendar' ),

        'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1

         && 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )  

    )

)

  1. Create measure MonthsValue

                    MonthsValue = [SalesCum]-[PMTD Sales]

 

  1. Now plot the Month from Calendar Table, Business Unit, Sales cumm., PMTDSales and

MonthsValue.

  1. You should get the desired results.

 

 If this works for you please accept this as solution and also give KUDOS.

 

Cheers 

 

CheenuSing

 

Sample screen shot

 

Capture.GIF

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Calculation of monthly figures from YTD for serveral groups

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
ChrisZ Frequent Visitor
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

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 

Community Support Team
Community Support Team

Re: Calculation of monthly figures from YTD for serveral groups

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.

 

 Capture.PNG

 

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:

 

Capture2.PNG

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
ChrisZ Frequent Visitor
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

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.

 

DateBusiness UnitSales YTD
31.01.2016ABC388.286,40
28.02.2016ABC1.128.132,20
31.03.2016ABC1.742.896,10
30.04.2016ABC2.196.540,98
31.05.2016ABC2.654.778,87
30.06.2016ABC3.295.550,83
31.07.2016ABC3.847.266,86
31.08.2016ABC4.210.714,27
30.09.2016ABC4.749.356,21
31.10.2016ABC5.351.003,68
30.11.2016ABC5.808.109,29
31.01.2016DEF436.728,00
28.02.2016DEF924.156,41
31.03.2016DEF1.512.040,40
30.04.2016DEF2.173.242,52
31.05.2016DEF2.629.141,96
30.06.2016DEF3.036.177,26
31.07.2016DEF3.774.962,39
31.08.2016DEF4.340.396,10
30.09.2016DEF4.885.096,11
31.10.2016DEF5.796.794,08
30.11.2016DEF

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

Super User
Super User

Re: Calculation of monthly figures from YTD for serveral groups

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.

  1. Create a calendar table using

Calendar = Calendar(Min(CumSales[Date]),max(CumSales[Date]))

Set the column Date to format dd/mm/yyyyy.

  1. Create column month in the calendar table

Month = Month([Date])

  1. Create column in calendar table

MonthDayNumber = DAY(Calendar[Date])

  1. Create column in calendar table

MonthDays = COUNTROWS ( FILTER( 'Calendar',  'Calendar'[YearMonthNumber] = EARLIER ( 'Calendar'[YearMonthNumber] ) ) )

  1. Create column in calendar table

Year = YEAR('Calendar'[Date])

  1. Create column in calendar table

YearMonthNumber = YEar ([Date]) *12+Month([Date]) – 1

  1. Create column in calendar table

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

    )  )

    

  1. Define relationship between CumSales[Date] and Calendar[Date] columns.
  2. Create measure SalesCum = Sum(CumSales[monthly sales])

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.])

  1. Create measure PMTDSales

PMTD Sales = CALCULATE (

    [SalesCum],

    FILTER (

        ALL ( 'Calendar' ),

        'Calendar'[YearMonthNumber] = MAX ( 'Calendar'[YearMonthNumber] ) - 1

         && 'Calendar'[Date] <= MAX ( 'Calendar'[PMDate] )  

    )

)

  1. Create measure MonthsValue

                    MonthsValue = [SalesCum]-[PMTD Sales]

 

  1. Now plot the Month from Calendar Table, Business Unit, Sales cumm., PMTDSales and

MonthsValue.

  1. You should get the desired results.

 

 If this works for you please accept this as solution and also give KUDOS.

 

Cheers 

 

CheenuSing

 

Sample screen shot

 

Capture.GIF

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
ChrisZ Frequent Visitor
Frequent Visitor

Re: Calculation of monthly figures from YTD for serveral groups

Hi @CheenuSing

 

GREAT! it works, thank you very much for your support.

 

Christian