topic Max Value per Quarter in DAX Commands and Tips

Max Value per Quarter

crjackson — Tue, 14 May 2024 15:54:20 GMT

I am working on effort certification which happens once a quarter. I am comparing what they certify vs the hours they actualy work per fiscal quarter/year. Our FY starts in September so they would certify on 9/1, 12/1, 4/1 and 7/1. I can't get the max to work once i filter by fiscal month even though it is within the same quarter. So if someone says they will do 20% effort on 9/1. I want to be able to use that percentage for Sep - Nov. It seems to work in September but when I change to October, it is blank even though it is within the quarter.

Re: Max Value per Quarter

Anonymous — Wed, 15 May 2024 01:55:44 GMT

Hi crjackson ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Quarterly Effort % = VAR QuarterStart = DATE ( YEAR ( 'Table'[Date] ), MONTH ( 'Table'[Date] ), 1 ) VAR QuarterEnd = EOMONTH ( QuarterStart, 2 ) RETURN CALCULATE ( AVERAGE ( 'Table'[EffortPercentage] ), FILTER ( 'Table', 'Table'[Date] >= QuarterStart && 'Table'[Date] <= QuarterEnd ) )

Best Regards

Yilong Zhou

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

Re: Max Value per Quarter

crjackson — Wed, 29 May 2024 15:27:33 GMT

So the issue with this is I don't want to have to populate a row for each physician each month. They fill out the certification form once a Fiscal Quarter so the table looks like this:

I would like what they enter in September "20%" to count for the entire quarter so September, October and November. I was trying to calculate the Max per Fiscal Quarter however the problem I am running into is that as soon as the slicer changes to October (within the same quarter), it renders a blank.