topic Re: Dax Average Days by Calendar Date in DAX Commands and Tips

Dax Average Days by Calendar Date

woodrow912 — Thu, 30 Sep 2021 04:27:55 GMT

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

Re: Dax Average Days by Calendar Date

amitchandak — Thu, 30 Sep 2021 09:27:45 GMT

woodrow912 ,

Check if two of my blogs can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

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-or-end-date/ba-p/1503785

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Dax Average Days by Calendar Date

woodrow912 — Thu, 30 Sep 2021 14:02:21 GMT

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.