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