How to show recent month and future months in X axis for a Rolling12Mths KPI in the Bar chart

[JasonXieee]

Hello PBI folk,

I struggle to achieve one thing in PBI Desktop.

I want to build a bar chart to show the average days to complete a contract (rolling past 12 months).

My fact table is simple. Each row means one contract, with an open date and a closed date. The duration of a contract then is the last column (days to complete). I have a 3-year record here; below is a sample. Additionally, I have imported a regular calendar table via DAX in order to show and filter dates in my dashboard.

So back to my bar chart visual:

1. For the X axis, I use "end of month Format" from the calendar table. I create a measure to show it in the format I want:

"End of Month",EOMONTH([Date],0) 

End of Month Format = FORMAT('Calendar table'[End of Month], "MMMYY")

2. For the Y axis, I put the measure2 below to calculate the <average rolling 12 months>.

Note: I put [Date Live] in the DATESINPERIOD because I want to use [Date Live] as what period the contract belongs to (like contract date). So for example, when I show the <Jan25 P12M Average>, it considers the contracts whose [Date Live] is in the past 12 months since Jan25. 

Measure1: 
Finish_Time = DATEDIFF('Contract Tracker'[Date Live], 'Contract Tracker'[Date Closed] , DAY)

Measure2:
Avg_Finish_Time_R12M = 
CALCULATE(
    AVERAGE('Contract Tracker'[Finish_Time]),
    DATESINPERIOD( 'Contract Tracker'[Date Live],MAX('Sort Month Name_AvgComple'[End of Month]), -12,  MONTH )
)

3. For the filter part, I only filter the "End of month" as below, so that I can show the past 12 months only. 

My question is: no matter how I filter, the latest date in my bar chart is only until Jan25. Theoretically, since I am calculating P12M Average, there should be data for Mar25, Apr25...until later 12 months in the future. But no matter how I clear and change the filter, I cannot even get the recent month Feb25 (maybe because it is still incomplete in my data).

May I know how I can adjust my X axis or the filter area so that I can show the Feb25 data? Thank you so much. 

Have a nice week,

Jason

[grazitti_sapna]

Hi @JasonXieee,

Your issue likely comes from the fact that your Avg_Finish_Time_R12M measure is filtering based on DATESINPERIOD(), but the available dates in your Contract Tracker table only go up to January 2025. Since DATESINPERIOD() works within the context of your fact table's [Date Live], it may not generate future periods unless they already exist in the dataset.

Since you want the rolling average to extend beyond the last available contract date, your Calendar Table must have future dates (e.g., up to Dec 2025 or later). If your calendar only goes up to Jan 2025, then your X-axis won't extend beyond that. 

Calendar Table =
ADDCOLUMNS(
CALENDAR(DATE(2022,1,1), DATE(2026,12,31)),
"End of Month", EOMONTH([Date],0)
)

Currently, your measure filters on 'Contract Tracker'[Date Live], but to include months with no data, it should reference 'Calendar Table'[End of Month] instead.

Modify your measure as below:-

Avg_Finish_Time_R12M =
VAR LatestDate = MAX('Calendar Table'[End of Month])
RETURN
CALCULATE(
AVERAGE('Contract Tracker'[Finish_Time]),
DATESINPERIOD( 'Calendar Table'[End of Month], LatestDate, -12, MONTH )
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[JasonXieee]

Hi Grazitti_sapna,

Thanks for your detailed reply! I tried your solution, but sadly it doesn't work. I think there are two issues:

1. Actually my contract data goes up to Feb 25 as shown (though not till the end of the month), while my bar chart only shows until Jan25. This is one puzzle I cannot figure out.

2. Your adjustment in my final measure (avg 12-month completion time) will make the measure irrelevant with any live date of my contract table (only related to the finish time). Therefore, all the months in my bar chart will have the same number. What I want to achieve is: the live date decides which month the contract is in. So when we say <Avg Contract Times in P12M> from Jan 25, that means we consider those contracts started in P12M. I guess we anyhow need to build a connection between the <live date> and my X-axis.

Hope to hear from you again. Thank you 🙂

[grazitti_sapna · Accepted solution]

Hi @JasonXieee,

Sure, It sounds like the key issue is ensuring that the rolling average considers Contracts by Live Date, while also making sure the X-axis (Calendar Table) extends properly.

Your chart stops at Jan 2025, even though contracts exist in Feb 2025. This could be due to:

• A missing relationship between Calendar Table and Contract Tracker.
• Your Calendar Table doesn’t extend far enough.

Check Relationship

Make sure there is a relationship:

• ‘Calendar Table’[Date] (1) → ‘Contract Tracker’[Date Live] (M)
• Ensure the X-axis in the chart comes from ‘Calendar Table’[End of Month].

Ensure Future Dates Exist

Modify your Calendar Table DAX to include future dates up to at least Dec 2026:
Calendar Table =
ADDCOLUMNS(
CALENDAR(DATE(2022,1,1), DATE(2026,12,31)),
"End of Month", EOMONTH([Date], 0)
)

Corrected DAX for Correct Rolling 12-Month Calculation,

Avg_Finish_Time_R12M =
VAR LatestMonth = MAX('Calendar Table'[End of Month])
RETURN
CALCULATE(
AVERAGE('Contract Tracker'[Finish_Time]),
DATESINPERIOD( 'Calendar Table'[End of Month], LatestMonth, -12, MONTH ), //Use DATESINPERIOD() based on Calendar Table[End of Month].
'Contract Tracker'[Date Live] <= LatestMonth // Filter contracts where Live Date falls within the past 12 months.
)

Use Calendar table End of month on the X axis,

make sure this relationship exists ‘Calendar Table’[Date] → ‘Contract Tracker’[Date Live]

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[JasonXieee]

Sorry for the late reply. Thank you for your answer.

Per my experiment, I have come up with a new idea to achieve it. That is using TREATAS(). Therefore, I do not have to create a data relation between the fact table and the calendar table. What I did was treat the 12-month calendar period as the date live to filter the past 12 months in the calculation function.

Thank you for your help. I will accept yours as a solution as well.