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

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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

    • 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?

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        You are welcome.  You may download my revised PBI file from here.

        Hope this helps.

  • 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