Forum Discussion

mlleverino's avatar
mlleverino
Helper I
9 years ago
Solved

Retrieving last count recorded

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.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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])

8 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      mlleverino
      Helper I
      9 years ago

      Hey Phil,

       

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