Forum Discussion

Beho's avatar
Beho
Helper 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
Super 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
    Helper 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.