cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAXRichArd Member
Member

Passengers Per Day Each Way (PPDEW) division by time series selected

Hello,

I work with commercial aviation data. There is a measure in the industry referred to as PPDEW (passengers per day each way).

 

BACKGROUND

The calculation is simple division:

  • Take the total number of one way passengers.
  • Divide by the total number of days in the time series.

If I have 730 passengers and my time series is 365 days then my PPDEW is 2.

730 / 365 = 2

 

If I have 84 passengers and my time series is 28 days then my PPDEW is 3.

84 / 28 = 3

 

My first example simulates a calendar year, and my second example simulates the month of February (non-leap year).

 

PROBLEM STATEMENT

I am working in both Power Pivot and Power BI.

 

I want to correctly calculate the PPDEW based on the time series I select when using slicers in a Pivot Table or fliters in Power BI.

 

Typically I would write a function that reads:

 

PPDEW :=
DIVIDE ( SUM ( Data[Total Pax] ), 365 )

This calculates correctly as long as my matrix layout is for years only. But if I filter for a month, the calculation will not be correct.

 

Thank you in advance for all your help.

DAXRichard

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

@DAXRichArd  few things:

 

- do you have calendar/date dimension in your model, if yes, great, if not then you need to add one and there are tons of blog available for it.

 

- once you have calendar dimension, use that for years/months/quarters etc and instead of dividing by 365, you divide by count of number of rows from calendar table, it will be 365 in case you are viewing at year level, it will be 28/30 or 31 if you are looking at month level and same will work for quarter.

 

And that will get you what you are looking, one measure and will work based on what calendar level you are looking at. Hope it is helpful, if you need more help, just provide sample data and I can get you the solution.

 

Thanks,

P






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

DAXRichArd Member
Member

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

Thanks parry2k. I've ran an initial test and it seems to be working. I'll circle back if I face a challenge I can't resolve. Thx and have a great week!

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

Try using a demoninator like:

 

Measure = COUNTROWS(GENERATESERIES(MIN([Date]),MAX([Date]),1))

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

@DAXRichArd  few things:

 

- do you have calendar/date dimension in your model, if yes, great, if not then you need to add one and there are tons of blog available for it.

 

- once you have calendar dimension, use that for years/months/quarters etc and instead of dividing by 365, you divide by count of number of rows from calendar table, it will be 365 in case you are viewing at year level, it will be 28/30 or 31 if you are looking at month level and same will work for quarter.

 

And that will get you what you are looking, one measure and will work based on what calendar level you are looking at. Hope it is helpful, if you need more help, just provide sample data and I can get you the solution.

 

Thanks,

P






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

DAXRichArd Member
Member

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

Thanks Greg!

Unfortunately I'm working in Excel PowerPivot (MicroSoft Office Professional Plus 2016).  The only 'generate' functions available are GENERATE and GENERATEALL. I'll try in PowerBI when I have a moment. I just wanted to acknowledge your response and thank you for your time.

DAXRichArd Member
Member

Re: Passengers Per Day Each Way (PPDEW) division by time series selected

Thanks parry2k. I've ran an initial test and it seems to be working. I'll circle back if I face a challenge I can't resolve. Thx and have a great week!

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)