Is this possible in Dax? Please respond to this ASAP!

[jacobmiller2322]

Hi,

I am trying to do a report related to forecasting and would like to know if what I am trying to do is possible.

So I have a lot of data (2,028,000 rows) and all of my data has a currency value attached, a date, as well as one of 23 categories it falls under assigned to it, but some of it also has a quantity and "cut" assigned to it, but that is only data that falls into the raw category. I am trying to forecast a future cost by taking all the data that has no quantity and doesnt full under 'Raw" and multipying those currency values by a selected percent increase, but also be able to add that with a selected increase in quantity for the future multiplied by a price per pound by month for each cut that changes throughout the year that is entered into excel beforehand.

Example: This would be previous years data, as well as the price entry table

Currency Value      Group             Date             Quantity   Cut       

700                        Parts              1/7/22

1500                      Raw                1/17/22         250           Ham

2000                      Freight           2/17/22

1000                      Parts              3/22/22

3500                      Raw               4/27/22         300            Belly

2300                      Parts              6/18/22

500                        Raw               8/12/22         250            Belly

450                        Parts              10/25/22

670                        Raw               11/13/22       175            Ham

Price Entry:

Month     Cut     Price

Jan          Ham    1.77

Feb         Ham     2.25

Mar        Ham      1.55

Apr         Ham      1.73

May        Ham      2.33

Jun         Ham      1.93

Jul          Ham       2.03

Aug        Ham       1.77

Sep        Ham        2.22

Oct         Ham       2.45

Nov        Ham       1.83

Dec        Ham        2.13

Jan         Belly        1.18

Feb        Belly        1.45

Mar        Belly        1.37

Apr        Be;;y         1.67

May       Belly         1.72

Jun        Belly          1.66

Jul         Belly          1.33

Aug       Belly          1.89

Sep        Belly          1.97

Oct        Belly           2.12

Nov       Belly           1.34

Dec       Belly            1.55

I basically would like to be able to take all the currency values that are not raw (6450) and multiply that by a selected % increase, and then add that with the quantity (975) multiplied by a percent increase and then those increased values multiplied by the assigned value per month by the cut and sum that all up to get the total forecasted cost of all the categories combined, raw and all.

Thanks

[Greg_Deckler]

@jacobmiller2322 All of the items that are not raw are Parts and Freight. But all the prices are Belly and Ham, so what is the price to multiply by? Otherwise, the first part seems straight forward:

Measure = 
  VAR __Currency = SUMX(FILTER('Table',[Group] <> "Raw"),[Currency Value])
  VAR __Quantity = SUMX(FILTER('Table',[Group] <> "Raw"),[Quantity])
  VAR __Result = __Currency * <some percent> + __Quantity * <some percent>
RETURN
  __Result

[jacobmiller2322]

Hi Greg,

The prices are the predicted price per pound for the month for that cut of raw material. The currency amount is the total cost for the singular transaction for the previous year with last year’s prices per pound, but those prices are irrelevant to me. I do not want to just multiply the currency value by the increase for the raw group rows, I would ideally like to apply an increase to the poundage for the previous year and then multiply those poundage values by the price per pound for the month the transaction takes place. All the other groups are multiplied by the same % increase but raw is calculated completely different, but I would like one measure to combine the two to get a total cost.

[Greg_Deckler]

@jacobmiller2322 OK, let's take a slightly different approach. What are your expected results given the data you provided? And perhaps an explanation of how those results were achieved?

[jacobmiller2322]

@Greg_Deckler

So, I took the sample data I gave you and got the value that I would like to get. So to explain, I basically would like to take the currency amounts for all the transactions that are now in the "raw" grouping and multiply those by a selected percent increase value on a slicer (in the example I used 2%) and then would like to take that value and add it with the forecast raw cost which is calculated by taking the quantity of each transaction and factoring in a selected percent increase and then taking those forecasted quantities and having them multiply by a forecasted price per pound value that changes by month and by cut that is entered in an excel spreadsheet before forecasting is done. 

Here is an image of what I would like to achieve but done in excel.

Just like in excel I would appreciate one measure so that I can graph it on a line by month, group, etc., but if it takes more than one that is fine.

[Greg_Deckler · Accepted solution]

@jacobmiller2322 OK, this makes it so much clearer. Try something like this:

Measure = 
  VAR __PercInc = 1.02
  VAR __PercIncRaw = 1.03
  VAR __CurrencyNonRaw = SUMX(FILTER('Table',[Group] <> "Raw"),[Currency Value]) * __PercInc
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Table', [Group] = "Raw"),
      "__Month", FORMAT([Date],"mmm"),
      "__Year", YEAR([Date])
    )
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__Price", MAXX(FILTER('Price Entry', [Month] = [__Month] && [Year] = [__Year]),[Price])
    )
  VAR __Table2 = 
    ADDCOLUMNS(
      __Table1,
      "__Value", [Quantity] * __PercIncRaw * [__Price]
    )
  VAR __CurrencyRaw = SUMX(__Table2, [__Value])
  VAR __Result = __CurrencyNonRaw + __CurrencyRaw
RETURN
  __Result

[jacobmiller2322]

@Greg_Deckler Also, The beginning of the formula does not work as the VAR __PercInc just gives an error. I am just very confused my bad.

[v-rzhou-msft]

Hi @jacobmiller2322 ,

I think  Greg_Deckler  's measure should work, also you can remove Year in your caluclation.

"VAR" function will stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

Measure = 
  VAR __PercInc = 1.02
  VAR __PercIncRaw = 1.03
  VAR __CurrencyNonRaw = SUMX(FILTER('Table',[Group] <> "Raw"),[Currency Value]) * __PercInc
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Table', [Group] = "Raw"),
      "__Month", FORMAT([Date],"mmm")
    )
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__Price", MAXX(FILTER('Price Entry', [Month] = [__Month]),[Price])
    )
  VAR __Table2 = 
    ADDCOLUMNS(
      __Table1,
      "__Value", [Quantity] * __PercIncRaw * [__Price]
    )
  VAR __CurrencyRaw = SUMX(__Table2, [__Value])
  VAR __Result = __CurrencyNonRaw + __CurrencyRaw
RETURN
  __Result

This measure works on myside.

VAR keyword (DAX) - DAX | Microsoft Learn

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jacobmiller2322]

@Greg_Deckler Hi Greg,

Are the "VAR_" before everything necessary?