Limit Endless X-Axis to date

[TBenders]

Hi there,

I have a couple of graphs which gets values from measure in the future. Because of this, graphs go on endlessly. My field of business is Recruitment and I have added formula-examples that I'm using, but with fictional data for demonstration.

2 examples

1. Graphs include measures with "+ 0" in it so timepoints with no data also get plotted as a 0 instead of an interupted line.
   
   Candidates Contacted = CALCULATE(COUNTROWS(FILTER('Applications';'Applications'[Status] = "Contacted"))) +0
   
   
2. I use the following formula for running totals by date. Because I also want to count vacancies that are still open now and dont have a 'CloseDate' yet, I'm using The ISBLANK is to also count all vacancies that are still open:
   
   Open Vacancies by Date = CALCULATE(
    DISTINCTCOUNT(Vacancies[Vacancy ID]);
        FILTER(Vacancies;
            Vacancies[OpenDate] <= LASTDATE(CalendarDisconnected[Date]) &&
            (Vacancies[CloseDate] >= FIRSTDATE(CalendarDisconnected[Date]) ||
            ISBLANK(Vacancies[CloseDate]))
   ))
   
   
   

The only way I know of to have the X-Axis end on the current month is to use a month-filter and manually move it forward 1 month on the 1st of each month. Using a report-level filter is not an option for my situation, so I'm doing this on page level. This is timeconsuming because of the large number of pages.

Is there a way around this by changing my measures so it doesnt have any data in the future and the X-Axis automaticly ends on the current date?

Thanks a lot for taking the time to help!

Tom 

[v-ljerr-msft · Accepted solution]

Hi @TBenders,

In this scenario, you can add a IF statement to tell if the date on X-Axis is less or equal that current date(TODAY()) first, then show the value you want accordingly. The formula below is for your reference.

Candidates Contacted =
IF (
    MAX ( CalendarDisconnected[Date] ) <= TODAY ();
    CALCULATE (
        COUNTROWS ( FILTER ( 'Applications'; 'Applications'[Status] = "Contacted" ) )
    )
        + 0
)

Regards

[TBenders]

Thanks a lot!