Previous Month to Date returning blank value

[BIsteht]

I need to calculate previous mtd, ytd, & week to date for use in KPI visuals.The below focuses on MTD. I have Sales and DImDate tables connected in the model. Sales[Date] is connected to DimDate[Date]. DimDate table contains consecutive values. The formulas below were found in forums, but both return (Blank).

Stack Overflow

Total Revenue MTD Last Month = TOTALMTD(SUM(SalesTable[revenue]),DATEADD(FILTER(DATESMTD(DimDate[Date]),DimDate[Date]<TODAY()),-1,MONTH))

MSDN post 

Revenue Prior MTD:=CALCULATE([Total Revenue],DATEADD(DATESMTD(DimDate[Date]),-1,MONTH)) 

Any ideas why I am getting a blank here? I don't find that anyone else has run into this issue in the posts linked above.

[anandav]

Hi @BIsteht,

Check this blog. When you understand the blog you can use the below DAX.

Total MTD =
TOTALMTD(SUM(PrevMonth[Amount]),
    DATESINPERIOD(MyDates[Date],
        LASTDATE(MyDates[Date]),
        SELECTEDVALUE(MyMonthsTable[Past Months], 0), 
        MONTH
    )
)

If you do not need the time intervalto be dynamic as explained in the blog, just replace SELECTEDVALUE(MyMonthsTable[Past Months], 0) with -1.

Hope this helps.

If this solves your problem please mark as solution.

[BIsteht]

@anandav Thanks for the quick response. Unfortunately this also results in blank.  My formula adpated from yours:

Total Revenue MTD Last Month = TOTALMTD(SUM(SQLQuery[revenue]),DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-1,MONTH))

I feel like calculating Previous MTD, QTD, YTD should be much easier than I have made it out to be. Any additional thoughts? Again, thanks for the help.

[anandav]

Hi @BIsteht,

Are you trying to create a new column or measure?

If it is a measure it should work as explained in the blog.

Can you upload your Power BI .pbix file with sample data (not your actual production data) to a cloud drive and share the link?

[BIsteht]

I am adding a measure. Everything I have tried comes back with no result (blank). It will take me a couple days to obscure data and provide.

[v-chuncz-msft · Accepted solution]

@BIsteht,

For any time intelligence function, you could implement a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[BIsteht]

I have reviewed the referenced link and, after testing, it looks like the values are calculating correctly. The issue is that I cannot get the KPI visual set up to disaply properly. I will search and open a different topic if needed. Thanks for the help!