cancel
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

Objective:

 

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

 

Solution: 

 

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))
Column:
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 : https://github.com/amitchandakpbi/powerbi/blob/main/DistributebetweenDays.pbix

 

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403

 

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