I’ve been struggling with this one for a few days and it’s something simple I’m overlooking. I just need to return the Prior Month Average for Each Customer and this works fine when Row Context is Month-Year, but doesn’t when Row Context is Date.
bumping this post
Bump to the top, still haven’t solved this
Hi @sscott,
Thank you very much for post your query into the forum.
I share a posible solution where I have created two measures:
Freight/lb by Customer -1M =
VAR _CurrentDate = MAX( Dates[Date] )
VAR _CurrentMonth = MONTH( _CurrentDate )
VAR _CurrentYear = YEAR( _CurrentDate )
VAR _PreviosMonth = IF(
_CurrentMonth = 1,
12,
_CurrentMonth - 1
)
VAR _PreviosYear = IF(
_CurrentMonth = 1,
_CurrentYear - 1,
_CurrentYear
)
VAR _DatesLM = FILTER(
ALL( Dates ),
Dates[Month Number] = _PreviosMonth &&
Dates[Year] = _PreviosYear
)
VAR _VirtualTable = ADDCOLUMNS(
VALUES( Freight[Customer Number] ),
"@Freight/lb", CALCULATE(
[Freight/lb],
_DatesLM,
ALLEXCEPT( Freight, Freight[Customer Number] )
)
)
VAR _Result = MAXX( _VirtualTable, [@Freight/lb] )
RETURN
_Result
//CONCATENATEX( _VirtualTable , [Customer Number] &":"&[@Freight/lb] &UNICHAR( 10 ) )
Freight/lb Result =
VAR _Freight_Status = MAX( Freight[Freight Status] )
VAR _Result = IF(
_Freight_Status = "No Freight", [Freight/lb by Customer -1M],
[Freight/lb]
)
RETURN
_Result
I have used the code commented at the end of the first measure:
//CONCATENATEX( _VirtualTable , [Customer Number] &":"&[@Freight/lb] &UNICHAR( 10 ) )
to debug the content of the virtual table, thanks to this post by @BrianJ on Linkedin.
I hope it can help you.
Regards
Freight Sample v2_JAFP.pbix (62.0 KB)
Thanks for your response @jafernandezpuga!
Hi @sscott, did the response provided by Jose help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Hi @sscott, we noticed that no response was received from you with regard to our post above.
We just want to check if you still need further help with this post.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Thank you very much for your response, I’m going to give this a try and will let you know. Much Appreciated!
Hi @sscott, did the response provided by @jafernandezpuga help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Hi @sscott, we noticed that no response was received from you with regard to our post last Oct 18th.
We just want to check if you still need further help with this post.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hello @sscott it’s been a while since we got a response from you. In case there won’t be any activity on it by Friday, Nov. 4, we’ll be tagging this post as Solved.
Thank you!
Hi @sscott,
I’d like to check again with you if the answer @jafernandezpuga provided above helped you solve your inquiry.
If it does, kindly mark the answer as the solution that solved your query.
If not, how far did you get, and what kind of help do you need further?
Hello @sscott
I would like to check with you again if the solution provided solved your inquiry.
If it does, kindly mark his answer as the solution to your query.
If not, how far did you get, and what kind of help do you need further?
If we do not receive any feedback from you, we’ll tag the solution provided by @jafernandezpuga as the solution.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @sscott due to inactivity, we’ll be marking this post as solved.
Should you wish to reopen this thread, kindly provide the PBIX file the experts requested above so other users can help you further.
