Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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)
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.
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.
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)
what kind of calculation do you expect to perform with the measure?
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.
can you provide a sample dataset and expected result table or matrix, fully reflecting your scenario?
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))
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |