topic Calculating Direct Material from AX 2012 with DAX function in DAX Commands and Tips

Calculating Direct Material from AX 2012 with DAX function

DF8859 — Tue, 21 Feb 2023 08:08:27 GMT

Hi,

Pretty new to Power BI and DAX but I'm having problem calculating a DAX formula getting some information from Microsoft AX 2012. Trying to get the the SUM of all components with certain costgroups to present as a column in this Power BI table, which is the sales a period selected with a slicer.

This SQL does the job when I have found the right "PriceCalcID":
Select SUM(SALESPRICEQTY) from BOMCALCTRANS where PRICECALCID like '058256' and ( COSTGROUPID like 'Mtrl' OR COSTGROUPID like 'SF' )

Trying to this but know I'm missing something. I need first to find the latest date in the selected period.
And for that certain date I need to find the PriceCalcID for the Product No in the above Power BI table.

DM =

VAR DMLastdate = LASTDATE(BOMCALCTRANS[TRANSDATE]) // Find the latest price change date

VAR DMPriceCalcID = SELECTEDVALUE(BOMCALCTRANS[PRICECALCID]) // Find the specific ID for the latest pricechange

VAR DMSumPriceSales =

CALCULATE(SUM(BOMCALCTRANS[SALESPRICEQTY]),

FILTER(BOMCALCTRANS, BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID] = "Mtrl")

)

Return DMSumPriceSales

Thank you very much in advance 🙏

Daniel

Re: Calculating Direct Material from AX 2012 with DAX function

Anonymous — Thu, 23 Feb 2023 01:58:32 GMT

Hi DF8859 ,

Please have a try.

Create a measure.

DM = VAR _DMLastdate = MAXX ( ALLSELECTED ( BOMCALATRANS ), BOMCALATRANS[TRANSDATE] ) RETURN CALCULATE ( SUM ( BOMCALCTRANS[SALESPRICEQTY] ), FILTER ( ALL ( BOMCALCTRANS ), ( BOMCALCTRANS[COSTGROUPID] = "SF" || BOMCALCTRANS[COSTGROUPID] = "Mtrl" ) && BOMCALATRANS[TRANSDATE] = _DMLastdate && BOMCALCTRANS[PRICECALCID] = SELECTEDVALUE ( BOMCALCTRANS[PRICECALCID] ) ) )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Re: Calculating Direct Material from AX 2012 with DAX function

DF8859 — Thu, 23 Feb 2023 08:13:44 GMT

Hi,

Managed to get a bit further yesterday.

Greated a new table called DM where I filtered posts within a period and categories.
-------------------------------------------------------------------------

DM = CALCULATETABLE(BOMCALCTRANS,DATESBETWEEN(BOMCALCTRANS[TRANSDATE],"2023-01-01", "2023-01-31"),BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="FP")
----------------------------------------------------------------------------------------------------------

Then I created a new Column, DM, in that new table and also created a relationship with another table.
This new table sums all Material and SF products by the PRICECALCID.
-------------------------------------------------------------------------------------------------------

DM = CALCULATE(SUM(DM[SALESPRICEQTY]),ALLEXCEPT(DM,DM[PRICECALCID]), BOMCALCTRANS[COSTGROUPID]="Mtrl" || BOMCALCTRANS[COSTGROUPID]="SF")
-------------------------------------------------------------------------------------------------------

For now the limitation is the static dates that I set when I create the new table. I would like
to med able to choose the dates from the date slicer.
My next thing to work on is to create a Matrix with three DM values for the Q1 quarter and show sales for each month.

Thank you very much for your support Anonymous 🙏