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('Op...
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.

lennyt99's avatar
lennyt99
Helper I
3 years ago

Sorry, So this Is what I get: 

Also for some reason at the start of the fiscal year, it's not returning the previous month volume. 

 

If I try and add Fiscal Month then I get this: 

Also, any idea how to get the grand total to display at the bottom? I've tried SUmx

edhans's avatar
edhans
Community Champion
3 years ago

The total is different. You need something like the following for your measure:

Volume LM2 =
VAR varCurrentMonth =
    MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
    CALCULATE(
        MAX( 'Fiscal Year Conversion'[YearMonth] ),
        'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
    )
VAR varMonthlyTotal =
    CALCULATE(
        SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
        'Fiscal Year Conversion'[YearMonth] = varPreviousMonth
    )
VAR Result =
    IF(
        ISINSCOPE( 'Fiscal Year Conversion'[FY] ),
        varMonthlyTotal,
        SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] )
    )
RETURN
    Result

At the total line, the year will not be "in scope" because it isn't visible, so it will just sum the annual volume.

 

As for the other issues, without an image of the model and a better understanding of your date table, I cannot say specifically.