adding data value with start and end dates for all dates

[EventHorizon99]

I am a newbie so please be gentle :

assume the following sample data table

Team	Start Date	End Date	Value
A	1/01/2019	1/01/2022	6
A	1/01/2021	1/01/2023	4
E	1/01/2019	1/01/2021	1
F	1/01/2023	1/01/2025	2
C	1/01/2022	1/01/2023	3
D	1/01/2019	1/01/2021	1

using DAX measure

DEFINE 
MEASURE 'Sample Data'[a2] = VAR StartDate = MIN('Sample Data'[Start Date])
VAR EndDate = MIN('Sample Data'[End Date])
VAR dat=
    ADDCOLUMNS(
        Calendar, "Data",
        IF(
            AND(
                'Calendar'[Date] >= StartDate,
                'Calendar'[Date] < EndDate
            ),
            CALCULATE(
                SUM('Sample Data'[Value]),
                FILTER(
                    'Sample Data',
                    'Calendar'[Date] >= 'Sample Data'[Start Date] &&
                    'Calendar'[Date] < 'Sample Data'[End Date]
                )
            ),
            0
        )
    )

RETURN
DIVIDE(SUMX(dat, [Data]),COUNTX(dat, [Data]))

My output:

Issue 1:  I would like  COUNTX to exclude nil(0) values so I can calculate portions of years

Issue 2:  The Grand Totals for columns are inconsistent and incorrect (see 2019, 2023);  

Issue 3: Is this "correct use" of variables; I sort of recall I may have broken some rules doing this  - bad or just VERY bad!

Notes:

1/I expect the Grand Total for rows is OK assuming I(we) fix the Count problem in issue 1. But fix 1 thing at a time

2/Apologies to using "value" as column name, I know - bad!

[EventHorizon99 · Accepted solution]

Updated:

I have used this thread to solve my problem. https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Calculate-the-sum-of-values-for-a-year-wi...

[Edit] However I had to modify slightly as my rate was per year. Rather than divide by 365.25 I opted to use the 1st day of month for Crossjoin and therefore divide by 12.  Since my minimum duration is a month; this works out fine.

this is my DAX followed by sum (not shown here)

Workload Per Month =

VAR PerMonthWorkload =

    ADDCOLUMNS (

        'Sample Data',

        "WorkloadPerMonth", DIVIDE ( 'Sample Data'[Value], 12)

    )

VAR DailyTable =

    CROSSJOIN (FILTER('Calendar', DAY([Date])=1), PerMonthWorkload )

VAR Result =

    FILTER (

        DailyTable,

        [Date] >= [Start Date]

            && [Date] < [End Date]

    )

RETURN

    Result

[EventHorizon99 · Accepted solution]

responded too soon; still not resolved. workload per day does not align value which is per year value. I need to divide by number of days in a year for this to work.

[EventHorizon99 · Accepted solution]

Updated:

I have used this thread to solve my problem. https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Calculate-the-sum-of-values-for-a-year-wi...

[Edit] However I had to modify slightly as my rate was per year. Rather than divide by 365.25 I opted to use the 1st day of month for Crossjoin and therefore divide by 12.  Since my minimum duration is a month; this works out fine.

this is my DAX followed by sum (not shown here)

Workload Per Month =

VAR PerMonthWorkload =

    ADDCOLUMNS (

        'Sample Data',

        "WorkloadPerMonth", DIVIDE ( 'Sample Data'[Value], 12)

    )

VAR DailyTable =

    CROSSJOIN (FILTER('Calendar', DAY([Date])=1), PerMonthWorkload )

VAR Result =

    FILTER (

        DailyTable,

        [Date] >= [Start Date]

            && [Date] < [End Date]

    )

RETURN

    Result

[FreemanZ]

hi @EventHorizon99 

what kind of calculation do you expect to perform with the measure?

[EventHorizon99]

Here is the table again

Team	Start Date	End Date	Value
A	1/01/2019	1/01/2022	6
A	1/01/2021	1/01/2023	4
E	1/01/2019	1/01/2021	1
F	1/01/2023	1/01/2025	2
C	1/01/2022	1/01/2023	3
D	1/01/2019	1/01/2021	1

The measure is attempting to calculate sum of value in sample data table for a given range of dates in the calendar. In this case "value" is the actual $ per year expended by  a Team. Hence I am trying to determine the total $ for a year. For Team A; it has  

row 1

1/01/2019

1/01/2022

6

and 

row 2

1/01/2021

1/01/2023

4

so the $ in 2021 is 6+4 = 10 $.

Now this is bit trivial as I have purposely aligned the start and end dates to years. But actually the dates may start and end at any month during a year. Say, alternatively if Team A row 1 with value of 6 had end date 1/7/2021; we would have only 6 month or 50% of the year.

row 1

1/01/2019

1/07/2021

6

and

row 2

1/01/2021

1/01/2023

4

Hence total $ in 2021 is not 6* (6/12) + 4 = 7 $ averaged over the year.

To calcualate this, I establish a metric and add values for each calendar day that fall in between the start and end date; I then need to divide by the total number days in the year. (note 1) 

Note 1: Contrary to earlier post., I realised that Issue 2 is wrong (silly me). I need to include all rows in my denominator including "0",s to ensure average for the year.

[FreemanZ]

hi @EventHorizon99 

can you provide a sample dataset and expected result table or matrix, fully reflecting your scenario?

[EventHorizon99]

Before table

After table (used excel to generate it)

Team A of 2019 = 6 * (6/12) = 3

Grand Total of 2025 is 0 as no values in this year.

This is how I calculated the interim step (of number of months) in excel. This is not how I intended to do it in dax but helps in understanding the above table. Consider number of months as portion or prorata of a year.

Here is the formula I used in Excel in larger print

=MIN(MAX(IFERROR(DATEDIF($B2,F$1+1,"M"),-DATEDIF(F$1+1,$B2,"M")),0),MAX(IFERROR(DATEDIF(F$1+1,$C2,"M"),-DATEDIF($C2,F$1+1,"M"))+12,0),12,IFERROR(DATEDIF($B2,$C2,"M"),0))