Showing results for 
Search instead for 
Did you mean: 

How to divide/distribute values between start date or end date or count days across months/days



How to display data month wise, when values are provided between a start and end dates.




Step1: We have got the following data.


Screenshot 2020-10-26 15.52.47.png


Step 2: Created a date table with Month year as one of the columns


Date = CALENDAR( date(2019,01,01),date(2021,01,01))
Month Year = FORMAT('Date'[Date],"YYYYMM")



Screenshot 2020-10-26 15.52.59.png


Now we need to display this data by dates or month. For this, we need to split data by date.


Screenshot 2020-10-26 15.56.42.png


While the approach can be to create a table using a cross join and filter. We will this approach in our measures.



Day by Month = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),'Data'[id],'Date'[Date]),'Date'[Date]))

Value by day of Month = CALCULATE(SUMX(SUMMARIZE(filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),'Data'[id],'Date'[Date],Data[Value],Data[StartDate],Data[EndDate]),DIVIDE(Data[Value],DATEDIFF(Data[StartDate],Data[EndDate],day)+1)))



Screenshot 2020-10-26 15.56.53.png



Let us know what you think about this.

The file can found at :


You can get all my posts at


You can also follow my YouTube channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars