{"id":695,"date":"2021-04-29T09:23:00","date_gmt":"2021-04-29T13:23:00","guid":{"rendered":"http:\/\/www.markwheeler.com\/?p=695"},"modified":"2021-04-29T09:23:00","modified_gmt":"2021-04-29T13:23:00","slug":"power-bi-plan-vs-actual-line-charts","status":"publish","type":"post","link":"https:\/\/markwheeler.com\/?p=695","title":{"rendered":"Power BI: Plan vs Actual Line Charts"},"content":{"rendered":"\n

\"\"
<\/p>\n\n\n\n

Request was to show a Running Total of Job Vacancies and Filled Vacancies by Target Date.<\/p>\n\n\n\n

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”
    )
)<\/p>\n\n\n\n

RETURN<\/p>\n\n\n\n

IF (MAX(‘Calendar'[Date]) > TODAY(),
    BLANK(), x
)<\/p>\n\n\n\n


<\/p>\n","protected":false},"excerpt":{"rendered":"

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])    )) […]<\/p>\n","protected":false},"author":3,"featured_media":696,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[1],"tags":[83,84],"class_list":["post-695","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-power-bi","tag-power-bi-desktop"],"jetpack_featured_media_url":"https:\/\/markwheeler.com\/wp-content\/uploads\/2021\/04\/onboardvsneed.png","_links":{"self":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/695","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=695"}],"version-history":[{"count":0,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/695\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/media\/696"}],"wp:attachment":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=695"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=695"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=695"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}