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

 

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.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

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

      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's avatar
        Phil_Seamark
        Microsoft Employee
        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])