Forum Discussion

cocomy's avatar
cocomy
Icon for Resolver I rankResolver I
8 years ago
Solved

Monthly total by branch

Hi All,   I am trying to calculate monthly sales by branch from actual sales table. (if no sales, no data)   Date                          Branch           Sales        Items 1 Jan 2018         ...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi cocomy,

     

    Are you sure about your relationships?

     

    I made a mockup model with your setup and go the correct result make for each month:

     

     

    Regards,

    MFelix

cocomy's avatar
cocomy
Icon for Resolver I rankResolver I
8 years ago

Hi M Felix,

 

Yes, I created monthly calendar table and also added one column to actual sales to show monthly date to create relationship. (1 Jan 2018, 1 Feb 2018....)

 

Relationship is shown in different colour below.

 

(1) Actual

Date                          Branch           Sales        Items            Monthly Date

1 Jan 2018                 NY                 50            Lipstick         1 Jan 2018

15 Jan 2018               NY                 40            Mascara        1 Jan 2018

15 Jan 2018               LA                  20            Cream          1 Jan 2018

20 Jan 2018               Seattle           15            Lipstick         1 Jan 2018

20 Jan 2018               Seattle           30            Cream          1 Jan 2018

27 Jan 2018               LA                  25            Cream          1 Jan 2018

31 Jan 2018               NY                 50            Mascara       1 Jan 2018

31 Jan 2018               NY                 45            Mascara       1 Jan 2018

1 Feb 2018                 LA                 20            Cream          1 Feb 2018

continue'd

 

(2) Calender

Monthly date

1 Jan 2018

1 Feb 2018

1 Mar 2018

 

(3) Branch

Branch

NY

LA

Seattle

 

(4) Monthly Sales I am aiming for on vizualization is

(2)Monthly date   (3)Branch          (1)Sales

1 Jan 2018            NY                    185

1 Jan 2018            LA                     65

1 Jan 2018            Seattle              45

1 Feb 2018           NY

1 Feb 2018           LA

1 Feb 2018          Seattle

 

I like to try DAX fomula below but still struggling how to express month.  Could you please kindly help?

 

Monthly total =

VAR select_date =
    MIN (Calender[Monthly_date])
VAR end_Date = ????
VAR Branch_select =
    MAX (Branch[Branch])
RETURN
    CALCULATE (
        SUMX (Actual,[Sales]),
        FILTER (
            ALL (Actual),
           Actual[Monthly_date] <= select_date
                && ??? <=end_Date
                && Actual[Branch] = Branch_select
        )
    )

 

All the best,

cocomy

 

 

Ellie1979's avatar
Ellie1979
Regular Visitor
8 years ago

Not sure on the formula, does a matrix table not give you the result you need pulling in Calendar.MonthlyDate as row headers, Branch.Branch as the column headers and Actual.Sales as the values then making sure the aggregation is set to sum?