How to Calculate Sum untill start of month of a given date in DAX

Question

I would like to calculate Sum(QTY) until the start date of the month for a given date.

Basically I can calculate Sum(QTY) until given date in my measure like:

SumQTYTillDate:=CALCULATE(SUM([QTY]);FILTER(ALL(DimDateView[Date]);DimDateView[Date]<=MIN(DimDateView[Date])))

But I also would like to calculate Sum(QTY) for dates before 10/1/2015 - which is the first date of selected Date's month. I have changed above measure and used STARTOFMONTH function to find first day of the month for a given date like;

.......DimDateView[Date]<=STARTOFMONTH(MIN(DimDateView[Date]))))

but not avail, it gives me

"A function ‘MIN’ has been used in a True/False expression that is used as a table filter expression. This is not allowed."

What am I missing? How can I use STARTOFMONTH function in my measure?

Thanks.

greggyb greggyb · Accepted Answer · 2015-10-09T15:37:44

STARTOFMONTH() must take a reference to a column of type Date/Time. MIN() is a scalar value, not a column reference. Additionally, your measure wouldn't work, because STARTOFMONTH() is evaluated in the row context of your FILTER(). The upshot of all this is that you would get a measure which just sums [QTY] across the first of every month in your data.

The built in time intelligence functions tend to be unintuitive at best. I always suggest using your model and an appropriate FILTER() to get to what you want.

In your case, I'm not entirely sure what you're looking for, but I think you want the sum of [QTY] for all time before the start of the month that the date you've selected falls in. In this case it's really easy to do. Add a field to your date dimension, [MonthStartDate], which holds, for every date in the table, the date of the start of the month it falls in. Now you can write a measure as follows:

SumQTY=SUM(FactQTY[QTY])

SumQTYTilStartOfMonth:=
CALCULATE(
    [SumQTY]
    ;FILTER(
        ALL(DimDateView)
        ;DimDateView[Date] < MIN(DimDateView[MonthStartDate])
    )
)

How to Calculate Sum untill start of month of a given date in DAX

1 Answers