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, get a free DP-600 exam voucher to use by the end of 2024. Register now

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors