Forum Discussion

pkiddle1973's avatar
pkiddle1973
Frequent Visitor
6 years ago

Help please - Calculating sales £ per month

 

Hi,

 

I am struggling to find a solution to my problem:

 

I have a table where we have numerous calculated columns to give me Daily GP.  Now where I am struggling is then putting this into a Matrix to show the total Total GP for the related month.

 

I have a Date Table which gives me the total number of working days for the month.

 

I want to show for April 2020 - May 2020 - June 2020 etc etc the sum of all the daily GP that falls into that month * the number of working days for that month the sale falls into, but it could fall into a months as these are daily and could go on for 3, 6 + months.

 

Any help would be appreciated.

 

Many thanks,

 

Pete

 

 

 

 

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    I don't fully get it... but these might help you:

    https://www.youtube.com/watch?v=78d6mwR8GtA

    https://www.youtube.com/watch?v=_quTwyvDfG0

    First and foremost, you should know how to build models that will give you answers via SIMPLE DAX.

    And this might be of help too:

    https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

    I don't know anything about your data and model, hence my suggestion is to first learn how to build a correct star-schema model. Everything else comes from this.

    Best
    D
  • pkiddle1973's avatar
    pkiddle1973
    Frequent Visitor
    6 years ago

    Hi,

     

    Firstly, apologies, I re-read my post and it was garble!

     

    So, here's a re-write...

     

    I have a table that contains:  Employment Type | Start Date | End Date | Daily GP £

     

    I am trying to work out how to show the calculated total per month of each of the Employment Types in a Matrix Visual such as:

     

    Month 30/04/2020 31/05/2020 30/06/2020 31/07/2020 31/08/2020 30/09/2020 31/10/2020 30/11/2020 31/12/2020 31/01/2021 28/02/2021 31/03/2021
    Type            
    Employment Type 1£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 2£0.00£136,291.00£64,750.00£22,125.00£3,700.08£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 3£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 4£0.00£0.00£0.00£0.00£330.75£330.75£349.13£0.00£0.00£0.00£0.00£0.00
    Employment Type 5£0.00£0.00£3,876.00£3,672.00£3,672.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 6£0.00£283.50£299.25£283.50£555.66£555.66£586.53£0.00£0.00£0.00£0.00£0.00
    Employment Type 7£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 8£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 9£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Employment Type 10£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00
    Grand Total£29,465.00£153,489.08£81,587.52£48,361.28£19,736.08£18,496.71£20,954.53£31,160.79£21,780.74£24,907.46£20,455.78£16,694.72

     

    This is done in an Excel Pivottable.

     

    So, Employment Type 1 for example, it looks for all matching Employment Type 1 and adds up the total for each month that the employment type covers (start Date & End Date).

     

    Many thanks,

     

    Pete