Forum Discussion
adding data value with start and end dates for all dates
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-with-start-date-and/m-p/2352850
[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])RETURNResultresponded 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.
Super Userwhat 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.
- FreemanZ
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))