If it is the first date or last date use one aggregation; if any other day, use another aggregation

[ekaponkratova]

Let's assume I have the below dataset. 

What I need to create the below matrix where if it is the beginning or month end, I aggregate A or B in Category 1 but if it is any other day in a month but 1st or last, I am tagging A or B in Category 2 and calculate COUNT. I guess I need to use SWITCH, is not it?

[ChrisMendoza · Accepted solution]

@ekaponkratova - 

Maybe this is what you were looking for?

I created a Calculated Column, to get labels for Columns as:

Column = 
VAR startMonth = STARTOFMONTH(TableName[date])
VAR endMonth = ENDOFMONTH(TableName[date].[Date])

RETURN 
IF(
    TableName[date]=startMonth || TableName[date]=endMonth,
    "Category 1",
    "Category 2"
)

Then I created a Measure as:

Measure = 
SWITCH (
    TRUE (),
    STARTOFMONTH ( TableName[date] ) = MAX ( TableName[date] )
        || ENDOFMONTH ( TableName[date].[Date] ) = MAX ( TableName[date] ), SUM ( TableName[quantity] ),
    STARTOFMONTH ( TableName[date] ) <> MAX ( TableName[date] )
        || ENDOFMONTH ( TableName[date].[Date] ) <> MAX ( TableName[date] ), COUNTROWS ( TableName )
)

Results in the left matrix:

[ekaponkratova]

And you are officially genius. Just question. Why do we need the 2nd measure? Cannot I just use quantity?

[ChrisMendoza]

@ekaponkratova -

I thought you needed a SUM([quantity]) when Category 1 othewise COUNTROWS. The Measure is trying to do that.

[ekaponkratova]

Hm, I still need to test, but I what I did, I re-used your category and then, just dropped quantity to values and used sum as aggregation.