MOM not working.. Please help

[lennyt99]

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?

[lennyt99]

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 

[edhans]

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]

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]

@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?

[edhans · Accepted solution]

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]

@edhans So now that I have it sorted like this, How do I put in the fiscal month names instead of the 6 digit number of "YearMonth?" 

It seems when I try to drag in month names the volume LM dissappears...

Thank you so much for your help you have no idea how much I appreciate you

[edhans]

As long as this is a date table, you can keep whatever you want in the visual as long as it is at the same granularity of the YearMonth field we are working off of. If there is a year and month in the visual, then the filter context will return one YYYYMM value, and that is what the measure needs.

But again, with no images, no data, no image of the model, what you are asking me to do is like calling your mechanic over the phone and saying "my car is going RRrrrrRRRrmmRRRRkkkkk. Please tell me how to fix it."

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]

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]

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.