Forum Discussion
Resolver IMonthly total by branch
Resolver IHi 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
Super UserHi 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
MFelix,
Thank you very much again. Relationship was incorrect so I got the same number for all apparently.!
All the best,
cocomy