Calculate Season for Different Categories

[CMccown]

I have data that has different seasons dependent upon category (see tables) and need a way to calculate these differences. I have a standard date table and a separate table that contains a column for two different rate types. Any help would be greatly appreciated!

Residential:

Month	Season
January	Winter
February	Winter
March	Winter
April	Winter
May	Winter
June	Summer
July	Summer
August	Summer
September	Summer
October	Winter
November	Winter
December	Winter

Commercial

Month	Season
January	Winter
February	Winter
March	Winter
April	Winter
May	Summer
June	Summer
July	Summer
August	Summer
September	Summer
October	Summer
November	Winter
December	Winter

[v-yanjiang-msft · Accepted solution]

Hi @CMccown ,

According to your description, I create a sample to reproduce your problem.

There're four tables in the sample. Residential and Commercial tables are the same with yours. Here's the other two tables:

Cost table:

Cost table show the cost one day in different season.

Date table:

Suppose you want to calculate the cost of all days in each month per different category. Calculate two measures.

CommercialCost =
SUMX (
    FILTER ( 'Date', 'Date'[Month] = MAX ( 'Commercial'[Month] ) ),
    COUNTROWS ( 'Date' )
        * MAXX (
            FILTER ( 'Cost', 'Cost'[Season] = MAX ( 'Commercial'[Season] ) ),
            'Cost'[Cost]
        )
)

ResidentialCost =
SUMX (
    FILTER ( 'Date', 'Date'[Month] = MAX ( 'Residential'[Month] ) ),
    COUNTROWS ( 'Date' )
        * MAXX (
            FILTER ( 'Cost', 'Cost'[Season] = MAX ( 'Residential'[Season] ) ),
            'Cost'[Cost]
        )
)

Result:

Residential:

Commercial:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.