DAX to Power Query Conversion

[Anonymous]

Hey everyone, 

The below DAX function shows the delta between two weeks of forecasting. 

I am struggling to translate the following Measure in DAX into a Custom Column in M. 

WEEK_TO_WEEK FORECAST_DELTA =
VAR Total = SUM('FORECAST_DATE'[VALUE])
VAR ThisPeriod = MAX('FORECAST_DATE'[Week_Start_Date])
VAR MinCycleSelected = CALCULATE(MIN('FORECAST_DATE'[CYCLE]),ALLSELECTED('FORECAST_DATE'[CYCLE]))
VAR PreviousRowIndex = CALCULATE(MAX('FORECAST_DATE'[WEEK_START_DATE]),FILTER(ALLSELECTED('FORECAST_DATE'),'FORECAST_DATE'[WEEK_START_DATE]<MAX('FORECAST_DATE'[WEEK_START_DATE])))

RETURN
IF(MIN('FORECAST_DATE'[CYCLE]) = MinCycleSelected, 0,
Total - CALCULATE(SUM('FORECAST_DATE'[VALUE]),
FILTER(
ALLEXCEPT('FORECAST_DATE',
'FORECAST_DATE'[Fiscal Quarter],
'FORECAST_DATE'[SKU],
'FORECAST_DATE'[KEY_FIGURE]
),
'FORECAST_DATE'[WEEK_START_DATE] = PreviousRowIndex)))

Trying to translate into M so that the function isn't run at the report level, which for the record count I have in this report, makes a refresh take > 1 minute. 

Thanks so much for your help in advance. ❤️ 

[Anonymous]

FORECAST_DATA set 

Hey @Greg_Deckler , I was wondering if you'd had a chance to look over my question? 

[Anonymous]

@Greg_Deckler Thank you so much! 

SKU CYCLE Week_Start_Date VALUE
A 2021-W16 4/19/2021 0:00 1496
A 2021-W16 4/26/2021 0:00 1606
A 2021-W16 5/3/2021 0:00 1328
A 2021-W16 5/10/2021 0:00 1328
A 2021-W16 5/17/2021 0:00 1328
A 2021-W16 5/24/2021 0:00 1328
A 2021-W16 5/31/2021 0:00 1561.992
A 2021-W16 6/7/2021 0:00 1599.996
A 2021-W16 6/14/2021 0:00 1599.996
B 2021-W16 4/19/2021 0:00 2416
B 2021-W16 4/26/2021 0:00 2070
B 2021-W16 5/3/2021 0:00 706
B 2021-W16 5/10/2021 0:00 704
B 2021-W16 5/17/2021 0:00 704
B 2021-W16 5/24/2021 0:00 704
B 2021-W16 5/31/2021 0:00 829.992
B 2021-W16 6/7/2021 0:00 847.996
B 2021-W16 6/14/2021 0:00 849.996
C 2021-W16 8/30/2021 0:00 0
C 2021-W16 9/6/2021 0:00 -2451
C 2021-W16 9/13/2021 0:00 -7252
C 2021-W16 9/20/2021 0:00 -12053
C 2021-W16 9/27/2021 0:00 -16855
C 2021-W16 10/4/2021 0:00 -21590
C 2021-W16 10/11/2021 0:00 -26236
C 2021-W16 10/18/2021 0:00 -30882
C 2021-W16 10/25/2021 0:00 -35529
C 2021-W16 11/1/2021 0:00 -40175
A 2021-W19 4/19/2021 0:00 1571
A 2021-W19 4/26/2021 0:00 1681
A 2021-W19 5/3/2021 0:00 1403
A 2021-W19 5/10/2021 0:00 1403
A 2021-W19 5/17/2021 0:00 1403
A 2021-W19 5/24/2021 0:00 1403
A 2021-W19 5/31/2021 0:00 1636.992
A 2021-W19 6/7/2021 0:00 1674.996
A 2021-W19 6/14/2021 0:00 1674.996
B 2021-W19 4/19/2021 0:00 2491
B 2021-W19 4/26/2021 0:00 2145
B 2021-W19 5/3/2021 0:00 781
B 2021-W19 5/10/2021 0:00 779
B 2021-W19 5/17/2021 0:00 779
B 2021-W19 5/24/2021 0:00 779
B 2021-W19 5/31/2021 0:00 904.992
B 2021-W19 6/7/2021 0:00 922.996
B 2021-W19 6/14/2021 0:00 924.996
C 2021-W19 8/30/2021 0:00 75
C 2021-W19 9/6/2021 0:00 -2376
C 2021-W19 9/13/2021 0:00 -7177
C 2021-W19 9/20/2021 0:00 -11978
C 2021-W19 9/27/2021 0:00 -16780
C 2021-W19 10/4/2021 0:00 -21515
C 2021-W19 10/11/2021 0:00 -26161
C 2021-W19 10/18/2021 0:00 -30807
C 2021-W19 10/25/2021 0:00 -35454
C 2021-W19 11/1/2021 0:00 -40100

[edhans]

Can you explain what your expected output is? And use the links below to share data. That data you pasted is in one big column in Power BI or Excel. And provide an image or something of what the expcted output is. There is no "DAX to M" translation. They are very different products and will be approached very differently.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

@edhans haha yeah I realized that this morning. That's why I posted the dataset as a CSV in the thread. (I have attached for you below as well)

This DAX function calculates the delta between 2 planning cycles for a given SKU. (I've attached a .pbix example). While it works, for the size of data I am working with, the quantity of records puts a load in the GUI any time a report-user refreshes or filters the data. 

I had the thought that if the data manipulation could be done at the transformation level, vs. the report level, then the heavy lifting would be shifted to not impact the individual user. 

Removing the DAX function removes the refresh lag (up to 20 seconds per pivot filter action).

This is where I thought "hey maybe this DAX function can be made into a custom column, as it is the bottleneck slowing down the report". 

Is there a way to do that? 

.pbix Example 

Data Sample for .pbix 

Thank you so much in advance and I'm sorry for the poor communication. 

[Greg_Deckler]

@Anonymous Could you post some sample data so that this can be mocked up?