Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |