Custom Column help

[iioannou]

Hello,

I have the following custom column

Occupancy % MTD = SUM('Rooms'[Occupancy %]) / 'Almyra Rooms'[StatisticDateDay]

I would like to SUM all rows that have the same month and same year only and not all the records. Then I would be able to devide it by the date of the month and I should get the MTD...

For example, this is what I have in mind...

Occupancy % MTD = SUM('Almyra Rooms'[Occupancy %] #WHERE MONTH NAME#) / 'Almyra Rooms'[StatisticDateDay]

[v-yuta-msft · Accepted solution]

Hi iioannou ,

Try this DAX formula:

Occupancy % MTD =
CALCULATE (
    SUM ( 'Rooms'[Occupancy %] ),
    FILTER (
        Rooms,
        YEAR ( Romms[date] ) = YEAR ( EARLIER ( Romms[date] ) )
            && MONTH ( Romms[date] ) = MONTH ( EARLIER ( Romms[date] ) )
    )
)
    / 'Almyra Rooms'[StatisticDateDay]

Regards,

Jimmy Tao