various shenanigans.

Power BI: Plan vs Actual Line Charts


Request was to show a Running Total of Job Vacancies and Filled Vacancies by Target Date.

First, I created a Calendar table: Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
Second, I had to create a Running Total of Job Vacancies from Jan 1st thru the Target Date.

CountTotalRunning = CALCULATE(
    COUNTA(‘Vacancies'[ID]),
    FILTER(
        ALLSELECTED(‘Vacancies’),
             ‘Vacancies'[Need By Date] <=MAX(‘Calendar'[Date])
    )
)

Next, I had to create a Running Total of Job Vacancies that have been Filled to-date. The stumper was getting the Filled line to stop at Today’s date.

CountTotalOnboardToday = VAR x = CALCULATE(
    COUNTA(‘Vacancies'[ID]),
    FILTER(
        ALLSELECTED(‘Vacancies’),
             ‘Vacancies'[Status] = “Filled”
    )
)

RETURN

IF (MAX(‘Calendar'[Date]) > TODAY(),
    BLANK(), x
)


Close Bitnami banner
Bitnami