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
Hello everyone. I am pretty new to Dax and have run into an issue with a formula I have been tryiung to work out. My data has personal information, or I would share it here.
I have a table titled Data with an [Event Date] which is the start date and [Event End], which is the end date. What I would like to do is create a pivot chart displaying every day, and the average days (start date to date on chart) for events where the start date is before the date (day) on the chart and the end date is after the date (day) on the chart. I am looking for what the average days was for each day in the chart. I hope that makes sense.
I have a screen shot of what i want the final product to resemble.
I created a date table and tried to get the average for each date in the date calendar, which seemed to work using the formula below. I converted the Event date to a number in order to work this out. But if I do it this way, i can't create slicers from my data, which I really need to do.
=VAR _StartDate= CALCULATE(AVERAGE(Data[Event Date Num]),FILTER('Data','Data'[Event Date] <= EARLIER('Datetable'[Date]) && 'Data'[Next Event]>= EARLIER('Datetable'[Date])) RETURN value(Datetable[Date])-_StartDate
I could really use some help here, and again, i apologize if its a little unclear.
Thanks
Check if two of my blogs can help
How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thanks for the quick response. I should also point out that I am using PowerPivot in Excel on a work computer and don't have access to Power BI. So I may not have the same capabilities of joining data.
I did look over your blogs, and they are very good. The first I am having a difficult time trying to work out. The second, seemed more straight forward. In the formula below from your blog, I am not sure what your doing with the DIVIDE(Data[Value] exaclty, and how I would be able to use that in my data.
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)))
I am not sure how to attach data to this reply. The only option I see is the table. But, I did save a sample .xlxs file here if that helps. wshanewood/sampledata (github.com) (https://github.com/wshanewood/sampledata).
I appreciate your help.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
24 | |
22 |