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.