Forum Discussion

lennyt99's avatar
lennyt99
Helper I
3 years ago
Solved

MOM not working.. Please help

 

I'm trying to display Volume Last month for my custom fiscal year (I already have the table linked, however it's not working. 

 

This is the formula:

Volume LM2 =
CALCULATE(
SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),
DATEADD('Fiscal Year Conversion'[Calendar Date],-1,MONTH))
 
It's giving the wrong number, I need it to return the anual 8,043,535 in FY19 for February LM volume
 
It's weird becase for Lat quarter it's working: 

and this is the formula: 

Volume LQ =
CALCULATE(
SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),
DATEADD('Fiscal Year Conversion'[Calendar Date],-1,quarter))
 
the same thing as the month, but quarter... Please help
 
Also for Fiscal year its off by a little the first two years:

and this is the formula: 

Volume LY2 =
CALCULATE(
SUM('Opportunity Tracker 2.0'[Annual Volume LBS]),
DATEADD('Fiscal Year Conversion'[Calendar Date],-1,YEAR)
)
 
 
Any suggestions?? I would really appreciate it. I've spend HOURS!!!
thank you!!
 
my fiscal year calendar is weird, some start the last week of the previous month, etc. any ideas?

 

Need Help
  • edhans's avatar
    edhans
    3 years ago

    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
    Result

     

    I 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.

9 Replies

  • edhans's avatar
    edhans
    Community Champion
    3 years ago

    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. 

    • lennyt99's avatar
      lennyt99
      Helper I
      3 years ago

      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 

      • lennyt99's avatar
        lennyt99
        Helper I
        3 years ago

        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
        )
        RETURN
        Result
         
        I 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?
  • lennyt99's avatar
    lennyt99
    Helper I
    3 years ago

    My appologies for not providing the data, I will do so next time I have a question. 

     

    It works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

     

    You've saved me a headache thank you thank you thank you thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  edhans