Graph shows rolling average for future months

[swestendorp]

Good day all, 

I can't wrap my head around the following issue. I've created a rolling 30 day measure. When visualing in a graph, values are shown for future months (when I use the date hierarchy). Obviously, I don't want these to be shown. How can I solve this? Your help is highly appreciated!

Final measure:

Rolling 30 days correct = IF(MAX(Dates[Date]) <= TODAY(),([Rolling30Days]), BLANK())

Created by following measures:

Rolling30Days = 1 - ([Rolling30Delay]/[Rolling30Voyages])

Rolling30Voyages =
CALCULATE (
    SUM ( VoyagesInPeriod[Duration] ),
    FILTER (
        ALLSELECTED ( 'Dates' ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
            && 'Dates'[Date]
                > MAX ( 'Dates'[Date] ) - 30
    )
)

Rolling30Delay =
IF (
    ISBLANK ( CALCULATE ( SUM ( DelaysInPeriod[Duration] ) ) ),
    BLANK (),
    CALCULATE (
        SUM ( DelaysInPeriod[Duration] ),
        FILTER (
            ALLSELECTED ( 'Dates' ),
            'Dates'[Date] <= MAX ( 'Dates'[Date] )
                && 'Dates'[Date]
                    > MAX ( 'Dates'[Date] ) - 30
        )
    )
)

Thanks a bunch, 

Sifra

[v-yuta-msft]

Hi swestendorp,

You said "Obviously, I don't want these to be shown. How can I solve this? Your help is highly appreciated!"

<--- Which column is used as X-axis in your chart? Could you give some more details about your table structure and expected requirement? And if possible, can you provide a pbix file with dummy data for further analysis?

Regards,

Jimmy Tao

[swestendorp]

Hi @v-yuta-msft,

Thanks for your reply. In the meantime, we have found out that it has something to do with the last step, that is the '1 - ...'. This is causing Power BI to calculate for future months. I don't know however to solve this. 

On the X-axis I have used my date table: 

Dates =
CALENDAR ( DATE ( ( 2013 ), ( 01 ), ( 01 ) ), TODAY () )

My data model looks like this:

Thanks for your help.

Best regards,

Sifra