Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
EventHorizon99
New Member

adding data value with start and end dates for all dates

I am a newbie so please be gentle :

 

assume the following sample data table

TeamStart DateEnd DateValue
1/01/2019 1/01/2022 6
1/01/2021 1/01/2023 4
1/01/2019 1/01/2021 1
1/01/2023 1/01/2025 2
1/01/2022 1/01/2023 3
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:

EventHorizon99_0-1676770290320.png

 

 

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!

2 ACCEPTED SOLUTIONS
EventHorizon99
New Member

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
 

 

 

View solution in original post

EventHorizon99
New Member

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.

View solution in original post

6 REPLIES 6
EventHorizon99
New Member

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
New Member

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
Super User
Super User

hi @EventHorizon99 

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

Here is the table again

 

Team Start Date End Date Value 
1/01/2019 1/01/2022 
1/01/2021 1/01/2023 
1/01/2019 1/01/2021 
1/01/2023 1/01/2025 
1/01/2022 1/01/2023 
1/01/2019 1/01/2021 

 

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 

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 

and

row 2

1/01/2021 1/01/2023 

 

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.

hi @EventHorizon99 

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

Before table

EventHorizon99_0-1676805640367.png

After table (used excel to generate it)

EventHorizon99_4-1676806530275.png

 

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.

EventHorizon99_5-1676806638440.png

 

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))

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.