Forum Discussion

TBenders's avatar
TBenders
Icon for Helper II rankHelper II
9 years ago
Solved

Limit Endless X-Axis to date

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 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

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

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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.:smileyhappy:

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

    Regards