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

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.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi mlleverino

     

    Any chance you can provide a longer dataset?

    • mlleverino's avatar
      mlleverino
      Helper I
      9 years ago
      Reporting PeriodFiscal YearFiscal QtrParent CustomerBU NameAmountAccount Type
      2016-04FY17FY17 Q1Customer ACT5Systems
      2016-05FY17FY17 Q1Customer ACT5Systems
      2016-06FY17FY17 Q1Customer ACT5Systems
      2016-07FY17FY17 Q2Customer ACT6Systems
      2016-08FY17FY17 Q2Customer ACT6Systems
      2016-09FY17FY17 Q2Customer ACT6Systems
      2016-10FY17FY17 Q3Customer ACT6Systems
      2016-11FY17FY17 Q3Customer ACT7Systems
      2016-12FY17FY17 Q3Customer ACT7Systems
      2016-05FY17FY17 Q1Customer AFSI4Systems
      2016-07FY17FY17 Q2Customer AFSI6Systems
      2016-04FY17FY17 Q1Customer AFSI6Systems
      2016-08FY17FY17 Q2Customer AFSI6Systems
      2016-10FY17FY17 Q3Customer AFSI7Systems
      2016-06FY17FY17 Q1Customer AFSI7Systems
      2016-11FY17FY17 Q3Customer AFSI8Systems
      2016-12FY17FY17 Q3Customer AFSI9Systems
      2016-09FY17FY17 Q2Customer AFSI10Systems
      2016-04FY17FY17 Q1Customer ACT             1,000Revenue
      2016-05FY17FY17 Q1Customer AFSI             1,575Revenue
      2016-07FY17FY17 Q2Customer AFSI             2,150Revenue
      2016-04FY17FY17 Q1Customer AFSI             2,725Revenue
      2016-08FY17FY17 Q2Customer AFSI             3,300Revenue
      2016-05FY17FY17 Q1Customer ACT             3,875Revenue
      2016-10FY17FY17 Q3Customer AFSI             4,450Revenue
      2016-06FY17FY17 Q1Customer AFSI             5,025Revenue
      2016-06FY17FY17 Q1Customer ACT             5,600Revenue
      2016-11FY17FY17 Q3Customer AFSI             6,175Revenue
      2016-07FY17FY17 Q2Customer ACT             6,750Revenue
      2016-12FY17FY17 Q3Customer AFSI             7,325Revenue
      2016-08FY17FY17 Q2Customer ACT             7,900Revenue
      2016-09FY17FY17 Q2Customer AFSI             8,475Revenue
      2016-09FY17FY17 Q2Customer ACT             9,050Revenue
      2016-10FY17FY17 Q3Customer ACT             9,625Revenue
      2016-11FY17FY17 Q3Customer ACT          10,200Revenue
      2016-12FY17FY17 Q3Customer ACT          10,775Revenue
      • mlleverino's avatar
        mlleverino
        Helper I
        9 years ago

        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.