Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Row level measure syntax: (Current year data) minus (SAMEPERIODLASTYEAR data) * Quantity

Hi,

 

I'm new to DAX... I am trying to create a measure that calculates (i.e. calculates row by row, then aggregates):

 

( (Current year sales / Current year quantity) - (Prior year sales / Prior year quantity) ) * (Current year quantity) = $

= ((Ave sell price) - (Ave sell price prior year)) * Current Quantity = $

= (ASP - ASP_LY) * QTY = $

 

The calc needs to occur at a row level first to get the full row $ result, then aggregate these $'s.

 

My attempts have beeen successful when tested as individual measures, i.e ASP and ASP_LY measures in the below picture show a result (which in their cases is a $/qty unit so as their own measure they give a meaningless agreggated result). For my desired measure ((ASP - ASP_LY) * QTY = $), I need these to all be calculated first at a row level, then aggreageted to return a $ amount. If not done at a row level the result is incorrect.

 

When I attempted what I thought as OK syntax to throw all within a SUMX, I do not get the result I want. I'm guessing that the ASP_LY which uses SAMEPERIODLASTYEAR term, inside the SUMX is filtering data out??

 

The end result is exactly the same as ASP measure result on its own, so it appears that the ASP_LY and QTY are not being picked up and calculated, or returning as 0.

 

I am using transaction level sales data for a table, so was hoping to create a measure that brought this together for my data analysis page. The intent for SAMEPERIODLAST YEAR is I have page filters, including a date one, to interogate different time periods.

 

Code is below, hope someone can help please...!

 

 

 

Pri-Vol Price =
sumx(
    //ASP
    AllSalesData,
    (divide(AllSalesData[value],AllSalesData[qty])-
   
    //ASP LY
    CALCULATE(
    sumx(AllSalesData,divide(AllSalesData[value],AllSalesData[qty])),
    SAMEPERIODLASTYEAR(
        AllSalesData[invoice_date].[Date]
    ))*
    //QTY
    AllSalesData[qty]
   
)
)

 

Thanks in advance, Mark

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

12 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Brilliant, that did the trick - thank you!

       

      I will check of as accepted, and have another I will raise now in a separate thread

       

      Mark

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        You are welcome.  If my previous reply helped, please mark that reply as Answer.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I have a few more if you can help Ashish_Mathur or Thennarasu_R 

       

      Re: my previous solultion, I also want to work out:

       

      Lost Volume = if(current year volume=0 & prior year volume>0), prior year sales)

       

      My code works at a row level, but of course fails at the total:

       

      Lost Volume = if([quantity]=0 && [quantity_LY]>0,sumx(values(AllSalesData[invoice_date].[Date]),-[revenue_LY]),0)

       

      When I try to wrap this in a sumx(values(... to get the total and rows to work, it does not work

       

      Hoping you can help there too?

       

      Mark

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Try this measure

        Lost volume = SUMX(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[order_no]),DATESBETWEEN('Calendar'[Date],edate(min('Calendar'[Date]),-12),min('Calendar'[Date])-1)),[order_no],"ABCD",[Quantity],"EFGH",[Quantity in PY],"IJKL",[Revenue in PY]),[ABCD]=0&&[EFGH]>0),[IJKL])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for the quick return Ashish.

     

    Hopefully the below makes sense, remebering I was trying to do in within a measure.

     

    1) Transaction level data:

     

    order_noinvoice_datestock_codeqtyvalue
    100001/01/2021Product A220.2
    1000115/01/2021Product D451.2
    100021/02/2021Product B793.8
    1000315/02/2021Product C578.5
    100041/03/2021Product B658.8
    1000515/03/2021Product E110.8
    100061/04/2021Product E331.2
    1000715/04/2021Product C8100.8
    100081/05/2021Product B9135
    1000915/05/2021Product A758.1
    100101/06/2021Product C774.9
    1001115/06/2021Product D116.4
    100121/07/2021Product A563
    1001315/07/2021Product A990.9
    100141/08/2021Product E676.8
    1001515/08/2021Product E453.6
    100161/09/2021Product D231.4
    1001715/09/2021Product A329.4
    100181/10/2021Product C775.6
    1001915/10/2021Product B883.2
    100201/11/2021Product D788.2
    1002115/11/2021Product B115
    100221/12/2021Product E541.5
    1002315/12/2021Product E553.5
    100241/01/2022Product B333.3
    1002515/01/2022Product C569
    100261/02/2022Product E8115.2
    1002715/02/2022Product D6100.2
    100281/03/2022Product C775.6
    1002915/03/2022Product E223.6
    100301/04/2022Product E445.6
    1003115/04/2022Product C9122.4
    100321/05/2022Product C10160
    1003315/05/2022Product A874.4
    100341/06/2022Product A893.6
    1003515/06/2022Product A234.8
    100361/07/2022Product C681.6
    1003715/07/2022Product C10111
    100381/08/2022Product C796.6
    1003915/08/2022Product C572
    100401/09/2022Product B350.1
    1004115/09/2022Product D443.2
    100421/10/2022Product D894.4
    1004315/10/2022Product E9102.6
    100441/11/2022Product B8108.8
    1004515/11/2022Product A232
    100461/12/2022Product B649.8
    1004715/12/2022Product E664.2

     

     

    2. Correct calculation result (row by row calulation then aggregation):

     

    ASP = value (2022) / qty (2022)

    ASP_LY = value (2021) / qty (2021)

    Price = (ASP - ASP_LY) * qty (2022)

     

     qtyqtyvaluevalue    
     2021202220212022 ASPASP_LYPrice
    Product A2620261.6234.8 11.7410.0633.57
    Product B3120385.8242 12.1012.45-6.90
    Product C2759329.8788.2 13.3612.2167.53
    Product D1418187.2237.8 13.2113.37-2.89
    Product E2429267.4351.2 12.1111.1428.09
            119.40

     

     

    3. Incorrect calculation result (aggregation then calculate formula)

     

    ASP = value (2022) / qty (2022)

    ASP_LY = value (2021) / qty (2021)

    Price = (ASP - ASP_LY) * qty (2022)

     

     qtyqtyvaluevalue    
     2021202220212022 ASPASP_LYPrice
    Product A2620261.6234.8 11.7410.0633.57
    Product B3120385.8242 12.1012.45-6.90
    Product C2759329.8788.2 13.3612.2167.53
    Product D1418187.2237.8 13.2113.37-2.89
    Product E2429267.4351.2 12.1111.1428.09
      146   62.5259.23479.78
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks Ashish, we are getting closer...

         

        Maybe because I gave you fictional data and not described my desired outcome you've created a literal version... I wish to be able to summarise the 'price' calculated by whatever view I choose. So if I take your response and change to view by month instead of product, I get an error in the ASP_LY and therefore price too.

         

        Sorry if I was not clear, I am trying to make this measure 'smart' and 'dynamic' so I can interogate data in whatever way I wish. Hence I though that using the SAMEPERIODLASTYEAR in the SUMX would somehow dynamically adjust to my desired view at the time...

         

        Does that make more sense?

         

         

    • Thennarasu_R's avatar
      Thennarasu_R
      Responsive Resident
      3 years ago

      1.ASP = VAR A=CALCULATE([VALUEMEASURE],(YEAR COLUMN)="2022")
                   VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2022")
      RETURN
           DIVIDE(A/B)

      2.mEASURE =VAR A=CALCULATE([VALUEMEASURE],(YEAR COLUMN)="2021")
                   VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2021")
      RETURN
           DIVIDE(A/B)

      3.Price = VAR a=SUMX(ASP - ASP_LY) 
                     VAR B=CALCULATE([QTYMEASURE],(YEAR COLUMN)="2022")
                  RETURN
                            SUMX(a*b)
      Try check this