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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DAXRichArd
Resolver I
Resolver I

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
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

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
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

Greg_Deckler
Super User
Super User

Try using a demoninator like:

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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