Forum Discussion

crjackson's avatar
crjackson
Icon for Helper I rankHelper I
2 years ago
Solved

Max Value per Quarter

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 ce...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

crjackson's avatar
crjackson
Icon for Helper I rankHelper I
2 years ago

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.