Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iioannou
Helper I
Helper I

Custom Column help

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]

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.