Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Anonymous

Hi, 

I cannot understand this formula, why do you have any value in May? it should be around 439. but it is zero

basically, it should be like this but the formula doesn't work I think 

 

Arsham_0-1612151521170.png

 

Anonymous

Thanks!

 

It worked wonderfully for me, once I disabled a relationship between de calendar table and the data table.

 

As I need this relationship for filtering purposes I suppose I will have to duplicate the calendar table.

Hi Amit,

Thank you for posting this solution.
I was able to follow your example with no issues as my dataset is the same as in your example.
Problem is,  I was asked  to exclude weekends
I've created a calculated column in sharepoint  to calculate business days, but I'm not sure how I could  use it in your DAX measure "Value by Day of Month"
Any suggestions?

Thank you in advance for your help.

 

 

Sorry, wrong comment

Anonymous

Hello, I followed your steps to separate the values by day. I was wondering if there is a way to now group all the data points into month buckets?

Hi, Is there a way to group the the day's values into month bucket?

Thank you for taking the time to explain this, I managed to get the solution working but like another user I had to break the relationship between my fact and date dimension

Hi there. I've tried the solution and i've got almost there.

 

Although, when the dates are the same the effort on my table isn't calculating. Can you help me?

 

Day by Month2 =
    CALCULATE(
        IF(
            COUNTX(
                SUMMARIZE(
                    FILTER(
                        CROSSJOIN('Resource Assignments', 'Alocação'),
                        'Alocação'[Date] >= 'Resource Assignments'[Start] && 'Alocação'[Date] <= 'Resource Assignments'[Finish]
                    ),
                    'Resource Assignments'[Task ID],
                    'Alocação'[Date]
                ),
                'Alocação'[Date]
            ) = 0,
            1,
            COUNTX(
                SUMMARIZE(
                    FILTER(
                        CROSSJOIN('Resource Assignments', 'Alocação'),
                        'Alocação'[Date] >= 'Resource Assignments'[Start] && 'Alocação'[Date] <= 'Resource Assignments'[Finish]
                    ),
                    'Resource Assignments'[Task ID],
                    'Alocação'[Date]
                ),
                'Alocação'[Date]
            )
        )
    )

@omelo You can exclude the weekends by adding the following to the filter:

&& WEEKDAY('Date'[Date],2)<6

@amitchandak Thank you for this, as it works perfectly! 

You absolutely saved my day. Thank you so much!