Weighted count of items by fraction of year held (by start date / end date)

[Anonymous]

Let's say I want to count the amount of items a company is holding throughout the years. How do I count items by the fraction of the year that they were held?

Example, Item A was held by a company from 2015.05.01 to 2018.03.04, represented in data as follows:

Item_name	date_begin (yyyy-mm-dd)	date_end (yyyy-mm-dd)
Item A	2015-05-01	2018-03-04

If I make a visual in powebi that shows the count of items held througout the years, this is what I want to see:

Item	2015	2016	2017	2018
Item A	0.668493	1	1	0.172603

Explanation:

0,668493 = (31-12-2015 minus 1-5-2015 = 244 days) / 365 days in 2015 = 244 / 365 = 0.668493

0.706849 = (4-3-2018 minus 1-1-2018 = 63 days) / 365 days = 63 / 365 = 0.172603

How do I define above weighted count in a DAX formula?

I can manage to count the occurance of an item in a year based on a seperate columns for start and end-date with below dax formula , but that doesnt give me a weighted count. I can't seem to make the next step...

Count_item_per_year = CALCULATE(COUNT(Table[Items]),
                          FILTER (  Table,
                                    Table[start_date] <= CALCULATE(MAX(calender[date])) &&
                                     Table[end_date] >= CALCULATE(MIN(calender[date]))))

[v-janeyg-msft]

Hi, @Anonymous 

According to your description,I create a year table(enter data) and a measure to calculate your desired result.

Like this:

Measure =
VAR begindate =
    SELECTEDVALUE ( 'Table'[date_begin] )
VAR enddate =
    SELECTEDVALUE ( 'Table'[date_end] )
VAR yearbegin =
    YEAR ( begindate )
VAR daybegin =
    DATEDIFF ( DATE ( yearbegin, 1, 1 ), DATE ( yearbegin, 12, 31 ), DAY ) + 1
VAR yearend =
    YEAR ( enddate )
VAR dayend =
    DATEDIFF ( DATE ( yearend, 1, 1 ), DATE ( yearend, 12, 31 ), DAY ) + 1
VAR yeartotal =
    SELECTEDVALUE ( 'YearTable'[Year] )
RETURN
    SWITCH (
        TRUE (),
        yearbegin = yeartotal, DIVIDE ( DATEDIFF ( begindate, DATE ( yearbegin, 12, 31 ), DAY ), daybegin ),
        yearend = yeartotal, DIVIDE ( DATEDIFF ( enddate, DATE ( yearend, 12, 31 ), DAY ), dayend ),
        yeartotal > yearbegin
            && yeartotal < yearend, 1
    )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your contribution and the time you took to make an example pbix!!! It's so close to what I'm looking for. Now, how would I make this measure summable (total remains empty) and/or make it work when there is a 'category'. See screenshot:

1. I can not get the measure to sum in the table;
2. I add a "parameter" to the data, if I make a graph or table, it only wants to show Parameter "Alfa", it does not show or sum parameter "Charlie" in the same graph/table, nor does it show anything when I only select Charlie in the slicer. I suspect this has to do with the problem I have in 1....

Is this solveable?

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Try:

Measure = 
SUMX(SUMMARIZE('Table',[date_begin],[date_end],[Item_name],"a",
VAR begindate =
    SELECTEDVALUE ( 'Table'[date_begin] )
VAR enddate =
    SELECTEDVALUE ( 'Table'[date_end] )
VAR yearbegin =
    YEAR ( begindate )
VAR daybegin =
    DATEDIFF ( DATE ( yearbegin, 1, 1 ), DATE ( yearbegin, 12, 31 ), DAY ) + 1
VAR yearend =
    YEAR ( enddate )
VAR dayend =
    DATEDIFF ( DATE ( yearend, 1, 1 ), DATE ( yearend, 12, 31 ), DAY ) + 1
    
VAR yeartotal = SELECTEDVALUE(YearTable[Year])
 return  SWITCH (
        TRUE (),
        yearbegin = yeartotal, DIVIDE ( DATEDIFF ( begindate, DATE ( yearbegin, 12, 31 ), DAY ), daybegin ),
        yearend = yeartotal, DIVIDE ( DATEDIFF ( enddate, DATE ( yearend, 12, 31 ), DAY ), dayend ),
        yeartotal > yearbegin
            && yeartotal < yearend, 1,
           yeartotal=0,
            DIVIDE ( DATEDIFF ( begindate, DATE ( yearbegin, 12, 31 ), DAY ), daybegin )+DIVIDE ( DATEDIFF ( enddate, DATE ( yearend, 12, 31 ), DAY ), dayend )+(yearend-yearbegin-1)               
)),[a])

You should know that the context of each visual is different, and a measure is not universal.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Here is one way to do it (this assumes that there is no relationship between your Date table and table with Item data).  Rename Items to your actual table name.

Pct of Year =
VAR vMinDate =
    MIN ( 'Date'[Date] )
VAR vMaxdate =
    MAX ( 'Date'[Date] )
VAR vItemStart =
    MIN ( Items[StartDate] )
VAR vItemEnd =
    MAX ( Items[EndDate] )
VAR vNewMin =
    MAX (
        vMinDate,
        vItemStart
    )
VAR vNewMax =
    MIN (
        vMaxdate,
        vItemEnd
    )
VAR vResult =
    DIVIDE (
        DATEDIFF (
            vNewMin,
            vNewMax,
            DAY
        ),
        DATEDIFF (
            vMinDate,
            vMaxdate,
            DAY
        )
    )
RETURN
    IF (
        vResult > 0,
        vResult
    )

Pat

[Anonymous]

Hi Pat thank you for your reply! But I have a hard time figuring this out 🙂

I'm probably misunderstanding what you posted, but it seems the end result of this is a fraction of a year (vresult?) but how does this do the weighted count for a year? (see the example I give for a non-weighted count for a year ("Count_item_per_year"), if I add this to a visual that has the years, this measure will add the non-weighted count of items in that year). 

How/where would I use your measure?