Last MTD Formula

[anelliaf]

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[Date]))

I have a similar formula for Last YTD total which is working.  No interaction is set with other filters.

Any ideas?

[amitchandak]

@anelliaf 

Try like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

. 

[anelliaf]

I tried using the formula:

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

It's still showing as BLANK.

[amitchandak]

Are you getting data for MTD. Have you selected any date or not. If you have not selected any date the MTD will start from the last month of the calendar.

[anelliaf]

That makes sense!

I want it to be dynamic.  How would I accomplish that?  Incorporating the TODAY function?

[v-xuding-msft]

Hi @anelliaf ,

 Do you try the way that amitchandak suggested? Have you resolved the problem? If yes, please accept the helpful answer as a solution. If you still need help, can you please share a dummy file? Then we will understand clearly about your situation.

[anelliaf]

I will be trying to apply his suggestion today.  I'll update the post after doing so.

[amitchandak]

@anelliaf 

There are a few ways.

1. Stop your calendar till today

2. Have a visual or page level filter on a date. Use relative option filter <= today. And MTD has its own time. Just today is also enough

[anelliaf]

My formula doesn't appear to be working:

Last MTD Costs = FORMAT(CALCULATE(SUM('Usage Details - 2'[Cost]), FILTER(DATESMTD(DATEADD(dateDimension[Date],-1,MONTH)) < TODAY())),"$0,000.00")

I tried several different atte

[v-xuding-msft]

Hi @anelliaf ,

Can you please share a few screenshots and sample data?

[anelliaf]

Here is a screenshot of the formula:

Here's a screenshot of the relationship:

[anelliaf]

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]

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]

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]

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]

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