Forum Discussion
MOM not working.. Please help
Sorry, try this:
Volume LM2 = VAR varCurrentMonth = MAX( 'Fiscal Year Conversion'[YearMonth] ) VAR varPreviousMonth = CALCULATE( MAX( 'Fiscal Year Conversion'[YearMonth] ), 'Fiscal Year Conversion'[YearMonth] < varCurrentMonth ) VAR Result = CALCULATE( SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ), 'Fiscal Year Conversion'[YearMonth] = varPreviousMonth ) RETURN ResultI am doing this without any data or model. If you need further help, please provide data, ideally a PBIX file via a share service (dropbox, onedrive, etc) that has no confidential data.
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.
Your fiscal calendar conversion must be marked as a date table. Also, if it is not a standard calendar, or a standard calendar that ends on a standard quarter (Mar 31, Jun 30, Sep 30, Dec 31) then you cannot use the built in time intelligence functions. You will have to roll your own function. For example:
Volume LM2 =
VAR varCurrentMonth =
MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
CALCULATE(
MAX( 'Fiscal Year Conversion'[YearMonth] ),
'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
)
VAR Result =
CALCULATE(
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
varPreviousMonth
)
RETURN
Result
The YearMonth field I made up here is a 6 digit integer. 202001 for Jan 2020, 202002 for Feb 2020, etc. You'd need that column, which is simple in Power Query or DAX. You just add a column of the Year * 100 + the month.
Now I can walk up and down those custom months by simply finding the max month where the yearmonth is less than the current yearmonth.
So I tried that and I got "the end of the input was reached" for Volume LM2.
Your completely right tho! Can you help me take it home please? edhans
edhans sorry, it's actually saying "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column"
I just had an extra ] last one but fixed it and after putting exactly this:
Volume LM2 =VAR varCurrentMonth =MAX( 'Fiscal Year Conversion'[YearMonth] )VAR varPreviousMonth =CALCULATE(MAX( 'Fiscal Year Conversion'[YearMonth] ),'Fiscal Year Conversion'[YearMonth] < varCurrentMonth)VAR Result =CALCULATE(SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),varPreviousMonth)RETURNResultI get the error"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."
How do I fix?Sorry, try this:
Volume LM2 = VAR varCurrentMonth = MAX( 'Fiscal Year Conversion'[YearMonth] ) VAR varPreviousMonth = CALCULATE( MAX( 'Fiscal Year Conversion'[YearMonth] ), 'Fiscal Year Conversion'[YearMonth] < varCurrentMonth ) VAR Result = CALCULATE( SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ), 'Fiscal Year Conversion'[YearMonth] = varPreviousMonth ) RETURN ResultI am doing this without any data or model. If you need further help, please provide data, ideally a PBIX file via a share service (dropbox, onedrive, etc) that has no confidential data.
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.