return FALSE; $r = well_tag_thread__update(array('id' => $id), $update); return $r; } function well_tag_thread_find($tagid, $page, $pagesize) { $arr = well_tag_thread__find(array('tagid' => $tagid), array('id' => -1), $page, $pagesize); return $arr; } function well_tag_thread_find_by_tid($tid, $page, $pagesize) { $arr = well_tag_thread__find(array('tid' => $tid), array(), $page, $pagesize); return $arr; } ?>DAX and powerbi - counting actively employed citizens over time - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>DAX and powerbi - counting actively employed citizens over time - Stack Overflow

    DAX and powerbi - counting actively employed citizens over time - Stack Overflow

    programmeradmin1浏览0评论

    I am trying to make a line chart that shows the unique count of citizens with active employment at a given time.

    I want the chart to count in the following way:

    If a citizen has a start date of employment of 20-07-2024 and an end date of 05-09-2024, this citizen should be included as active in July, August, and September of 2024. If a citizen has multiple overlapping employments, he/she should only be counted once in a given month. My model is quite simple and consists of a FACT table, which holds the start and end dates, and other information such as the full name of the citizen, place of employment, and so on, and a date table. I have a one-to-many relationship from the date table Dimension_Dato[date] to the FACT table Ansaettelser[Enddate].

    I made the following measure:

    aktive_ansaettelser_keepfilter = 
VAR MinDate = MIN(Dimension_Dato[Dato])
VAR MaxDate = MAX(Dimension_Dato[Dato])
VAR Result =
    CALCULATE(
        SUMX(DISTINCT(Ansaettelser[Ber]), 1),
        KEEPFILTERS(Ansaettelser[Startdato] <= MaxDate), 
        KEEPFILTERS(
            IF(
                OR(ISBLANK(Ansaettelser[Slutdato]), Ansaettelser[Slutdato] > TODAY()),
                TODAY() + 365,
                Ansaettelser[Slutdato]
            ) >= MinDate
        ),
        REMOVEFILTERS(Ansaettelser[Slutdato])
    )
RETURN
    Result

    However, when I double-check if all the relevant citizens are counted correctly in a given month, I find that several citizens are not included as they should be.

    Here is a list of citizens who are not counted in July, along with their start and end dates of employment:

    enter image description here

    What am i doing wrong? Any help is greatly appreciated

    I am trying to make a line chart that shows the unique count of citizens with active employment at a given time.

    I want the chart to count in the following way:

    If a citizen has a start date of employment of 20-07-2024 and an end date of 05-09-2024, this citizen should be included as active in July, August, and September of 2024. If a citizen has multiple overlapping employments, he/she should only be counted once in a given month. My model is quite simple and consists of a FACT table, which holds the start and end dates, and other information such as the full name of the citizen, place of employment, and so on, and a date table. I have a one-to-many relationship from the date table Dimension_Dato[date] to the FACT table Ansaettelser[Enddate].

    I made the following measure:

    aktive_ansaettelser_keepfilter = 
VAR MinDate = MIN(Dimension_Dato[Dato])
VAR MaxDate = MAX(Dimension_Dato[Dato])
VAR Result =
    CALCULATE(
        SUMX(DISTINCT(Ansaettelser[Ber]), 1),
        KEEPFILTERS(Ansaettelser[Startdato] <= MaxDate), 
        KEEPFILTERS(
            IF(
                OR(ISBLANK(Ansaettelser[Slutdato]), Ansaettelser[Slutdato] > TODAY()),
                TODAY() + 365,
                Ansaettelser[Slutdato]
            ) >= MinDate
        ),
        REMOVEFILTERS(Ansaettelser[Slutdato])
    )
RETURN
    Result

    However, when I double-check if all the relevant citizens are counted correctly in a given month, I find that several citizens are not included as they should be.

    Here is a list of citizens who are not counted in July, along with their start and end dates of employment:

    enter image description here

    What am i doing wrong? Any help is greatly appreciated

    • powerbi
    • dax
    • linechart
    Share Improve this question asked Mar 19 at 10:59 NielsNiels 13 bronze badges
    Add a comment  | 

    1 Answer 1

    Reset to default 0

    You should expand your date using Power Query and run the distinct count based on that Expanded Date field connected to a Calendar Table. In that case, you simplify your measure and rely on the storage engine instead of the formula engine.

    Assuming your data looks like this

    Fact Citizens

    Employee ID Start Date End Date
    10513 02/10/2023 20/10/2023
    10071 21/08/2023 04/01/2024
    10514 05/09/2023 13/04/2024
    10513 07/09/2023 30/06/2024
    10733 26/09/2023 21/03/2024
    10415 21/08/2023 30/08/2023
    10107 28/09/2023 06/06/2024
    10613 25/09/2023 21/03/2024
    10995 24/08/2023 30/04/2024
    10066 30/08/2023 16/12/2023
    10247 29/09/2023 30/09/2023

    Steps

    Step 1:

    Expand your dates using Power Query.

    Expand the dates of your StatDate and EndDate. I chosen to expand the dates only using the first day of the month, reducing the number of rows generated.

    The key element here is adding a new custom column, with the following code

    PQ: ExpandedTable column

        ExpandedDates = Table.AddColumn(Source, "ExpandedDate", each List.Distinct(List.Transform({Number.From([StartDate])..Number.From([EndDate])},each Date.StartOfMonth(Date.From(_))))),

    PQ: Complete Sample Code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDsUgCETv4roJAyLqWRrvf42KaX/QxV8OeTCP+06MwjldCUIMEogHwS+MyxlU9jET2sdACexBX6aw+rgQ+sdwpoltzFqtgckgWGRqXgoWGC/OkVEup4/fadF5XnKmhTuzaO+y5SPlX1fvq0uPru0vmO0K83cjlugjunz68fsbxngA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    ExpandedDates = Table.AddColumn(#"Changed Type", "ExpandedDate", each List.Distinct(List.Transform({Number.From([StartDate])..Number.From([EndDate])},each Date.StartOfMonth(Date.From(_))))),
    #"Expanded MonthStartDates" = Table.ExpandListColumn(ExpandedDates, "ExpandedDate"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded MonthStartDates",{{"ExpandedDate", type date}})
in
    #"Changed Type1"

    Step 2

    Create a calendar table based on the expanded date column.

    DAX: Calendar Table

    Calendar = 
ADDCOLUMNS(
    CALENDAR(min('Fact Citizens'[ExpandedDate]),max('Fact Citizens'[ExpandedDate])),
    "Month Year", FORMAT([Date],"mmm-yy"),
    "Month Year Index", FORMAT([Date],"yyyymm"),
    "Year", year([Date])
)

    Step 3

    Create a relationship between the Calendar table and the Fact Citizens table.

    Step 4

    Create the citizens distinct count measure

    Citizen Count = 
DISTINCTCOUNTNOBLANK('Fact Citizens'[Employee ID])

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论