Forum Discussion

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

Chart with constant limits

I have a table of data for which I want to create an upper limit based on the last 6 calendar month of data per category. See example: Issued Date Type Count 2/5/2022 New 234 4/2/2022 ...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
2 years ago

Beho it shouldn't, it should show a flat line for all the dates. Please provide more details or pbix file to look into it.

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

Hi Parry2k,

 

I tried adding the DAX as a measure and it wouldn't allow me to select the date field, so I added a calculated column:

 

 

6MM_Execution Erron Mean = 
VAR EndDate = IF ( MONTH ( 'Records Sheet'[Issued Date] ) <= 6, DATE ( YEAR ( 'Records Sheet'[Issued Date] ) - 1, 12, 31 ), DATE ( YEAR ( 'Records Sheet'[Issued Date] ), 6, 30 ) )
VAR StartDate = EOMONTH ( EndDate, -6 ) + 1

RETURN
DIVIDE(
    CALCULATE(
        COUNTROWS('Records Sheet'),
        'Records Sheet'[Type] = "Execution Error",
        'Records Sheet'[Issued Date] >= StartDate,
        'Records Sheet'[Issued Date] <= EndDate
    ),
    6,
    0
)

 

 

And when I add this column in my chart, it doesn't add anything.

Is that the right way to approach it? if I want to do it as a measure, is there a way to workaround not being able to select it.