Sum/Compare Month to Date vs Previous Month

[texasbiuser]

Hi,

I am trying to create a measure that Sums invoices for the previos month based on the day number of the month. I want the measure to be dynamic, so I don't have to update it each month. 

Example:

If Today is 9/21/2019, I then want to SUM all invoice data between 8/1/2019 and 8/21/2019. I have the measure I am trying to get to work below, but I just can't seem to figure out what I am doing wrong.

TotalFood_PreviousMonth =

CALCULATE(

Sum(MAX_Config_Orders[TotalFood]),

FILTER ( MAX_Config_Orders,

MAX_Config_Orders[InvoiceDate] >= EOMONTH(TODAY(),-2)+1 &&

MAX_Config_Orders[InvoiceDate] <= EOMonth(today(), -1)

)

)

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the InvoiceDate column of the MAX_Config_Orders Table to the Date column of the Calendar Table.

Write these measure

Measure1 = Sum(MAX_Config_Orders[TotalFood])

Measure2 = CALCULATE([Measure1],DATESBETWEEN(Calendar[Date],EOMONTH(TODAY(),-2)+1),EDATE(TODAY(),-1)))

Hope this helps.

[jdbuchanan71 · Accepted solution]

Hello @texasbiuser 

For my solution to work you will need a date table.  You can create a basic one using this code.  Modeling > New Table

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN 
ADDCOLUMNS(
    DateRange,
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Year Month", FORMAT([Date],"yyyy-mmmm"),
    "YearMonthSort",YEAR([Date])*100 + MONTH([Date]),
    "ShortName",FORMAT([Date],"ddd"),
    "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7}
)

Then we can stack together some time functions to get what you are looking for.

TotalFood_PreviousMonth =
VAR MinDate =
    FIRSTDATE ( MAX_Config_Orders[InvoiceDate] )
VAR MaxDate =
    LASTDATE ( MAX_Config_Orders[InvoiceDate] )
RETURN
    CALCULATE (
        SUM ( MAX_Config_Orders[TotalFood] ),
        DATEADD ( DATESBETWEEN ( Dates[Date], MinDate, MaxDate ), -1, MONTH )
    )

DATESBETWEEN ( Dates[Date], MinDate, MaxDate ) gives us a list of the dates, in your example 9/1/2019 - 9/21/2019.  This is the part where we need the date table.

DATEADD ( datelist, -1, MONTH ) shifts that list back by one month so we get 8/1/2019 - 8/21/2019.

That is the used in the CALCULATE over SUM ( MAX_Config_Orders[TotalFood] ).

[kentyler]

It might be interesting to look at DAX's TOTALMTD() function. It seems like you could calculate the dates you wanted and just feed them to the function.