DAX Previous Month to date total is giving entire previous month's entire total

Question

I am using DAX in Power BI to calculate Previous Month sales total to date to create a KPI visual. i.e. if today is 7th Dec then I want to get sales total from 1st Nov to 7th Nov and compare with current month to date.

CurrentMTD = TOTALMTD(SUM(SALES_VOUCHERS[SaleValue]),DatesTable[Date])

This works fine. However Previous Month YTD gives me total for entire month of November. I have tried the following so far

PMYTD = totalmtd(sum(SALES_VOUCHERS[SaleValue]),dateadd(DATESMTD(DatesTable[Date]),-1,month))

and

PMYTD = CALCULATE(sum(SALES_VOUCHERS[SaleValue]),
    DATESBETWEEN(DatesTable[Date],                    
        FIRSTDATE(PREVIOUSMONTH(DatesTable[Date])),   
        LASTDATE(DATEADD(DatesTable[Date],-1,MONTH))))

Both return the same answer which is total for the entire previous month . If I simply hardcode the start and end date in datesbetween version above, then I do get the desired result. But that is not the solution.

I have linked the fact table (Sales_VOUCHERS) to a DatesTable and as of now there are no other visuals on the report page. Kindly assist what I am missing out on and how I can get Previous Month year to date total

Leonard Leonard · Accepted Answer · 2016-12-07T21:38:13

If you're aggregating at the month level (i.e. you're looking at December 2016 vs. November 2016), then the measure you have above will show you the entire month of December compared to the entire month of November (and since December is a partial month and November isn't, it causes the mismatch you see).

If you filter to the current date (e.g. 7th Dec), then both your MTD and Prior Month MTD measures will only show you through the 7th of their corresponding months.

Assuming you don't want to filter to the day level (not unreasonable), you could enhance your formula to filter out future dates. For example:

 PMYTD = totalmtd(
    sum(SALES_VOUCHERS[SaleValue]),
    dateadd(
        FILTER(
            DATESMTD(DatesTable[Date]),
            DatesTable[Date]<TODAY()
        ),
        -1,
        month
    )
)

This says, if the date is after today, don't pass it into the TOTALMTD calculation (so it will only calculate the first 7 days of the month, for example, if today is Dec 8th - even if you're looking at full months on your report).

Side note: you can also write your previous month measure to re-use your MTD measure rather than redefining it. In this way, if you ever change the MTD calculation, the previous MTD calculation automatically updates.

PMYTD = CALCULATE(
    [CurrentMTD],
    DATEADD(
        FILTER(
            DatesTable[Date],
            DatesTable[Date]<TODAY()
        ),
        -1,
        MONTH
    )
)

Useful Resources:

https://www.powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/ (article that covers this problem and a variety of solutions)
https://community.powerbi.com/t5/Desktop/Compare-MTD-with-previous-period/td-p/24656 (forum discussion about the same problem)
http://community.powerbi.com/t5/Desktop/Time-Intelligence-TOTALMTD-vs-DATESMTD-vs-DATEADD/td-p/10088 (forum discussion about DATESMTD vs TOTALMTD)

DAX Previous Month to date total is giving entire previous month's entire total

1 Answers