Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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")
Now we need to display this data by dates or month. For this, we need to split data by date.
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)))
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.