To get total customer number as the count of max date for that particular year

[Anonymous]

Hi everyone,

I have following table and a calendar date table which is linked to this table so that I can use time intelligent function. I have a line graph to show number of customers for each supplier which can be drilled up and down for year and quarter respectively.  I have a problem in showing yearly data. Suppose these are the time period- 2018Q1, 2018Q2, 2018Q3, 2018Q4, currently I am using Avg of  (2018Q1, 2018Q2, 2018Q3, 2018Q4) Customer number to show yearly data but ideally I should use 2018Q4 count as the total customer count of 2018.  There is an option for Max but is taking max count of customer number.  Is there any way to show annual data as Q4 count of each year as total customer for that year.

Any help would be greatly appreciated.

Supplier	CustomerNumber	Type	Date	Market
A	5077	type1	01 January 2017	MarketA
A	7383	type1	01 April 2017	MarketA
A	2643	type1	01 July 2017	MarketA
A	9171	type1	01 October 2017	MarketA
A	9807	type1	01 January 2018	MarketA
A	2066	type1	01 April 2018	MarketA
A	7441	type1	01 July 2018	MarketA
A	3684	type1	01 October 2018	MarketA
B	2697	type1	01 January 2017	MarketA
B	9423	type1	01 April 2017	MarketA
B	8912	type1	01 July 2017	MarketA
B	8820	type1	01 October 2017	MarketA
B	6919	type1	01 January 2018	MarketA
B	5240	type1	01 April 2018	MarketA
B	7586	type1	01 July 2018	MarketA
B	1965	type1	01 October 2018	MarketA
A	1008	type2	01 January 2017	MarketA
A	5377	type2	01 April 2017	MarketA
A	1108	type2	01 July 2017	MarketA
A	9639	type2	01 October 2017	MarketA
A	2454	type2	01 January 2018	MarketA
A	4162	type2	01 April 2018	MarketA
A	1405	type2	01 July 2018	MarketA
A	1015	type2	01 October 2018	MarketA
A	4593	type2	01 January 2017	MarketB
A	9068	type2	01 April 2017	MarketB
A	9146	type2	01 July 2017	MarketB
A	4568	type2	01 October 2017	MarketB
A	7872	type2	01 January 2018	MarketB
A	1363	type2	01 April 2018	MarketB
A	6326	type2	01 July 2018	MarketB
A	5977	type2	01 October 2018	MarketB
B	6008	type2	01 January 2017	MarketA
B	7547	type2	01 April 2017	MarketA
B	6961	type2	01 July 2017	MarketA
B	5514	type2	01 October 2017	MarketA
B	6241	type2	01 January 2018	MarketA
B	2137	type2	01 April 2018	MarketA
B	2010	type2	01 July 2018	MarketA
B	8822	type2	01 October 2018	MarketA
B	2877	type2	01 January 2017	MarketB
B	4108	type2	01 April 2017	MarketB
B	7753	type2	01 July 2017	MarketB
B	5808	type2	01 October 2017	MarketB
B	4608	type2	01 January 2018	MarketB
B	7934	type2	01 April 2018	MarketB
B	6323	type2	01 July 2018	MarketB
B	7571	type2	01 October 2018	MarketB

[harshnathani]

Hi @Anonymous ,

Your requirement is not very clear, but you can try this.

Measure 8 = CALCULATE(Count(Supplier[CustomerNumber]))

If this is not whatyou are looking for please share expected output. Also Data is very symettrical, please share some variations. For e.g 3 in each quarter, 12 in the year.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Hi @harshnathani ,

Apologies that the question is not clear. I am plotting a line graph with date hierarchy. When I drill up to show yearly data, it sums up all the quarters data of that year. 

When we look at the yearly representation it is adding quarterly data. For ex- 2017, the total customer count for 2017 is sum of 2017Q1+2017Q2+2017Q3+2017Q4. 

My requirement is the total customer count for 2017 should be the value of 2017Q4 instead of the summation of all quarters i.e total customer count for 2017 should be 17355 and not 69615. 

Sample data-

Supplier	CustomerNumber	Type	Date	Market	Quarter
A	17718	type1	01 January 2017	MarketA	2017Q1
A	17435	type1	01 April 2017	MarketA	2017Q2
A	17107	type1	01 July 2017	MarketA	2017Q3
A	17355	type1	01 October 2017	MarketA	2017Q4
A	17262	type1	01 January 2018	MarketA	2018Q1
A	17214	type1	01 April 2018	MarketA	2018Q2
A	17261	type1	01 July 2018	MarketA	2018Q3
A	17094	type1	01 October 2018	MarketA	2018Q4
B	1047	type1	01 January 2017	MarketA	2017Q1
B	1057	type1	01 April 2017	MarketA	2017Q2
B	1037	type1	01 July 2017	MarketA	2017Q3
B	1003	type1	01 October 2017	MarketA	2017Q4
B	939	type1	01 January 2018	MarketA	2018Q1
B	904	type1	01 April 2018	MarketA	2018Q2
B	947	type1	01 July 2018	MarketA	2018Q3
B	1091	type1	01 October 2018	MarketA	2018Q4
A	1137411	type2	01 January 2017	MarketA	2017Q1
A	1130613	type2	01 April 2017	MarketA	2017Q2
A	1125576	type2	01 July 2017	MarketA	2017Q3
A	1123080	type2	01 October 2017	MarketA	2017Q4
A	1117091	type2	01 January 2018	MarketA	2018Q1
A	1110150	type2	01 April 2018	MarketA	2018Q2
A	1103428	type2	01 July 2018	MarketA	2018Q3
A	1100443	type2	01 October 2018	MarketA	2018Q4
A	144675	type2	01 January 2017	MarketB	2017Q1
A	141492	type2	01 April 2017	MarketB	2017Q2
A	140707	type2	01 July 2017	MarketB	2017Q3
A	140527	type2	01 October 2017	MarketB	2017Q4
A	140112	type2	01 January 2018	MarketB	2018Q1
A	139111	type2	01 April 2018	MarketB	2018Q2
A	139257	type2	01 July 2018	MarketB	2018Q3
A	140821	type2	01 October 2018	MarketB	2018Q4
B	328620	type2	01 January 2017	MarketA	2017Q1
B	332214	type2	01 April 2017	MarketA	2017Q2
B	332274	type2	01 July 2017	MarketA	2017Q3
B	330460	type2	01 October 2017	MarketA	2017Q4
B	331836	type2	01 January 2018	MarketA	2018Q1
B	337319	type2	01 April 2018	MarketA	2018Q2
B	342262	type2	01 July 2018	MarketA	2018Q3
B	348809	type2	01 October 2018	MarketA	2018Q4
B	327407	type2	01 January 2017	MarketB	2017Q1
B	323176	type2	01 April 2017	MarketB	2017Q2
B	316837	type2	01 July 2017	MarketB	2017Q3
B	311380	type2	01 October 2017	MarketB	2017Q4
B	307854	type2	01 January 2018	MarketB	2018Q1
B	305528	type2	01 April 2018	MarketB	2018Q2
B	303806	type2	01 July 2018	MarketB	2018Q3
B	304366	type2	01 October 2018	MarketB	2018Q4

[harshnathani · Accepted solution]

Hi @Anonymous ,

Please use below measure

Measure 7 = CALCULATE(SUM(Supplier[CustomerNumber]),FILTER(Supplier,Supplier[Date] = MAx(Supplier[Date])))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Hi @harshnathani ,

The solution worked like a charm. With the help of this I am able to calculate YoY and QoQ percentage change correctly. However, I am trying to  find the overall percentage share as per the selected data but not getting the desired result.

Earlier I used this measure-

SupplierPctAllSe = DIVIDE(SUM(ElectricityGasCustomerNum[CustomerNumber]),CALCULATE(SUM(ElectricityGasCustomerNum[CustomerNumber]), ALLSELECTED(('ElectricityGasCustomerNum'))))

[harshnathani]

Hi @Anonymous ,

Sorry typing this from my mobile.

But use ALL instead for ALLSelected for overall percentage.

Regards

Harsh Nathani

[Anonymous]

Hi @harshnathani ,

I actually want to use Allselected so that the data that is selected from the slicer is used as 100%. The desired result is in the screenshot provided.

Regards,

Supriya

[harshnathani]

Hi @Anonymous ,

Please use the following measures.

SUM_SUPPLIER = SUM(Supplier[CustomerNumber])

Total_Sum_Supplier = CALCULATE(SUM(Supplier[CustomerNumber]),ALLSELECTED(Supplier))   ----> Supplier is Name of the Table

PercentageSS = DIVIDE([SUM_SUPPLIER],[Total_Sum_Supplier],0)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Why is

distinctcount(Supplier[CustomerNumber])

not good? This is the exact number of customers in any period of time. Why would you want to calculate the yearly figure as the Q4 figure? That makes no sense.

Best
D

[mahoneypat]

I believe this measure will get you the desired result.  It calculates the sum for the last quarter (alphabetically) in any context.   When you are drilled down to quarter it still works.

LastQuarterCount = CALCULATE(SUM(Data[CustomerNumber]), TOPN(1, VALUES(Data[Quarter]), Data[Quarter],DESC))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat