cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

amitchandak

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

Comments
Polls
What is your favorite Power BI feature release for November 2023?