cancel
Showing results for
Did you mean:
Highlighted
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.

DAXRichard

2 ACCEPTED SOLUTIONS

Accepted Solutions
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

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

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!

4 REPLIES 4
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

Proud to be a Datanaut!

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

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

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.

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!

Announcements