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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
woodrow912
Frequent Visitor

Dax Average Days by Calendar Date

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 

 

 

 

screenshot.png

 

2 REPLIES 2
amitchandak
Super User
Super User

@woodrow912 ,

Check if two of my blogs can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

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.

@amitchandak ,

 

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.