Forum Discussion

anelliaf's avatar
anelliaf
Helper II
6 years ago

Last MTD Formula

I'm using the following Last MTD Costs formula to show a floating cost total in a card.  It's showing BLANK.   Last MTD Costs = CALCULATE(SUM('Usage Details - 2'[Cost]),PREVIOUSMONTH(dateDimension[...
Need Help
anelliaf's avatar
anelliaf
Helper II
6 years ago

Here is a screenshot of the formula:

 

 

Here's a screenshot of the relationship:

anelliaf's avatar
anelliaf
Helper II
6 years ago

Here's some more formulas I'm trying:

Last Month Sum = VAR endDate = LASTDATE(dateDimension[MonthDate])
RETURN
CALCULATE(
    SUM('Usage Details - 2'[Cost]),
    dateDimension[MonthDate] = endDate

...with MonthDate as the following:

MonthDate = DATE(YEAR(dateDimension[Date]), MONTH(dateDimension[Date]), 1)

 

Unsure why it seems so complicated to have a dynamic value for last months total.  It works when they're associated with the slicers/filters.  However, once I edit interactions I recieve the '(BLANK)' value.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi anelliaf ,

    Maybe you could have a try like this:

    Last MTD Costs =
VAR vLastMTD =
    FORMAT (
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            DATEADD ( DATESMTD ( 'Date'[Date] ), -1, MONTH )
        ),
        "$0.00"
    )
RETURN
    MAXX ( 'Table', vLastMTD )

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • anelliaf's avatar
    anelliaf
    Helper II
    6 years ago

    Hi Xue,

     

    I think I worded my post wrong.  I'm looking for a floating visual card showing last month's total costs, not last month to current date.

     

    In other words, I'm trying to show a dynamic visual where last month would be February costs, since we're in March.  Once we go into April, I'd like for the visual to dynamically change to show March costs since we'd be in April.

     

    Thanks,

    Frank

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi anelliaf ,

    In my sample, it is shown the value of the last month ( February's costs not contain March's) in Card visual. And in April, the data will be updated after refreshing. The value is not static. 

     

    Maybe I don't understand what you what clearly.  Can you please share some sample data and the correct output? 

     

  • anelliaf's avatar
    anelliaf
    Helper II
    6 years ago

    I'm not sure, but it showed blank in my report.