Graph shows rolling average for future months

Question

Good day all,&nbsp;&nbsp;I can't wrap my head around the following issue. I've created a rolling 30 day measure. When&nbsp;visualing&nbsp;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!&nbsp;Final measure:&nbsp;Rolling 30 days correct = IF(MAX(Dates[Date]) &lt;= TODAY(),([Rolling30Days]), BLANK())&nbsp;Created by following measures:&nbsp;Rolling30Days = 1 - ([Rolling30Delay]/[Rolling30Voyages])&nbsp;Rolling30Voyages&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;VoyagesInPeriod[Duration]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Dates'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Dates'[Date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Dates'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;&nbsp;MAX&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;-&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Rolling30Delay&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;DelaysInPeriod[Duration]&nbsp;)&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;DelaysInPeriod[Duration]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;'Dates'&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Dates'[Date]&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Dates'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;&nbsp;MAX&nbsp;(&nbsp;'Dates'[Date]&nbsp;)&nbsp;-&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Thanks a bunch,&nbsp;Sifra

v-yuta-msft · Answer

Hi&nbsp;swestendorp,
&nbsp;
You said "Obviously, I don't want these to be shown. How can I solve this? Your help is highly appreciated!"
&lt;--- 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?
&nbsp;
Regards,
Jimmy Tao

swestendorp · Answer

Hi v-yuta-msft,&nbsp;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.&nbsp;&nbsp;On the X-axis I have used my date table:&nbsp;&nbsp;Dates&nbsp;=CALENDAR&nbsp;(&nbsp;DATE&nbsp;(&nbsp;(&nbsp;2013&nbsp;),&nbsp;(&nbsp;01&nbsp;),&nbsp;(&nbsp;01&nbsp;)&nbsp;),&nbsp;TODAY&nbsp;()&nbsp;)&nbsp;My data model looks like this:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks for your help.&nbsp;Best regards,Sifra

Forum Discussion

Graph shows rolling average for future months

2 Replies

Related content

Rolling 12M Average measure

Datamart future

Rolling average exclude future dates

Rolling accumulation

3 Day Rolling Average - DAX