Retrieving last count recorded

[mlleverino]

Need help with DAX instruction to do the following.

REPORTING PERIOD         REVENUES      TOOL COUNT (as of this month)

2016-04                            1,000               2

2016-04                            1,000               2

2016-04                            1,000               2

for this QUARTER, revenues were 3,000 while the tool count remained at 2.  Thus, revenues per tool were 1,500

Which DAX instruction will allow me to do this?  my current instruction displays the 3,000 correctly, but it is also summing up the tool count.  Tool count should remain at 2, not 6.

[Phil_Seamark]

HI @mlleverino

Does your Tool Count ever decrease?

If not then this is the jist of what you are after

New Measure = DIVIDE(
			SUM(Table1[REVENUE]),
			MAX('Table1'[TOOL COUNT])
			)

Which if you use in a Visual and use a Quarter Field on the AXIS, should yeild the number you are after

[mlleverino]

Hey Phil,

Yes, tool counts do decrease as they become obsoleted or sold off to other customers.

[Phil_Seamark]

Hi @mlleverino

Any chance you can provide a longer dataset?

[mlleverino]

Reporting Period	Fiscal Year	Fiscal Qtr	Parent Customer	BU Name	Amount	Account Type
2016-04	FY17	FY17 Q1	Customer A	CT	5	Systems
2016-05	FY17	FY17 Q1	Customer A	CT	5	Systems
2016-06	FY17	FY17 Q1	Customer A	CT	5	Systems
2016-07	FY17	FY17 Q2	Customer A	CT	6	Systems
2016-08	FY17	FY17 Q2	Customer A	CT	6	Systems
2016-09	FY17	FY17 Q2	Customer A	CT	6	Systems
2016-10	FY17	FY17 Q3	Customer A	CT	6	Systems
2016-11	FY17	FY17 Q3	Customer A	CT	7	Systems
2016-12	FY17	FY17 Q3	Customer A	CT	7	Systems
2016-05	FY17	FY17 Q1	Customer A	FSI	4	Systems
2016-07	FY17	FY17 Q2	Customer A	FSI	6	Systems
2016-04	FY17	FY17 Q1	Customer A	FSI	6	Systems
2016-08	FY17	FY17 Q2	Customer A	FSI	6	Systems
2016-10	FY17	FY17 Q3	Customer A	FSI	7	Systems
2016-06	FY17	FY17 Q1	Customer A	FSI	7	Systems
2016-11	FY17	FY17 Q3	Customer A	FSI	8	Systems
2016-12	FY17	FY17 Q3	Customer A	FSI	9	Systems
2016-09	FY17	FY17 Q2	Customer A	FSI	10	Systems
2016-04	FY17	FY17 Q1	Customer A	CT	1,000	Revenue
2016-05	FY17	FY17 Q1	Customer A	FSI	1,575	Revenue
2016-07	FY17	FY17 Q2	Customer A	FSI	2,150	Revenue
2016-04	FY17	FY17 Q1	Customer A	FSI	2,725	Revenue
2016-08	FY17	FY17 Q2	Customer A	FSI	3,300	Revenue
2016-05	FY17	FY17 Q1	Customer A	CT	3,875	Revenue
2016-10	FY17	FY17 Q3	Customer A	FSI	4,450	Revenue
2016-06	FY17	FY17 Q1	Customer A	FSI	5,025	Revenue
2016-06	FY17	FY17 Q1	Customer A	CT	5,600	Revenue
2016-11	FY17	FY17 Q3	Customer A	FSI	6,175	Revenue
2016-07	FY17	FY17 Q2	Customer A	CT	6,750	Revenue
2016-12	FY17	FY17 Q3	Customer A	FSI	7,325	Revenue
2016-08	FY17	FY17 Q2	Customer A	CT	7,900	Revenue
2016-09	FY17	FY17 Q2	Customer A	FSI	8,475	Revenue
2016-09	FY17	FY17 Q2	Customer A	CT	9,050	Revenue
2016-10	FY17	FY17 Q3	Customer A	CT	9,625	Revenue
2016-11	FY17	FY17 Q3	Customer A	CT	10,200	Revenue
2016-12	FY17	FY17 Q3	Customer A	CT	10,775	Revenue

[Phil_Seamark]

Hi @mlleverino

Thanks for that.  In this case is the BU name the same as the Tool Count?

I'm guessing the Amount column = Revenue

[mlleverino]

Account Type - Systems = tool count for that reporting period and for that BU

Account Type - Revenue = revenues for that reporting period and for that BU.

if I were to look at a one BU for one reporting period, it will have 1 tool count entry and 1 revenue entry.

[Phil_Seamark · Accepted solution]

Hi @mlleverino

Here are three calculated measures that might be getting closer.  I don't think I fully understand the Tool Count measure but this is what I have for your sample data.  It might only be a slight tweak away from what you need.

Tool Count = CALCULATE(
                DISTINCTCOUNT('Table1'[Amount]),
                'Table1'[Account Type] ="Systems"
                )


Revenue = CALCULATE(
                SUM('Table1'[Amount]),
                'Table1'[Account Type] ="Revenue"
                )

Result = DIVIDE([Revenue],[Tool Count])

[mlleverino]

the dataset has all tool counts increasing, but there may be a chance they decrease.  the tool counts are total as of that month.  they are not additional tools to the previous month.