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

Show date for highest and lowest value in a time series

Hello,

Before requesting help I've hacked through the community and can't find a solution. I work with commercial aviation data (passengers, seat capacity, flight frequencies, other).

 

Problem Statement

  • Software - Power BI
  • Identify the maximum value and the minimum value for a long time series
    • Based on a single slicer or
    • Based on a combination of slicers

Calculation (Measure)

  • The max and min value I want to identify is not a column but a measure:
  • Passengers = SUM('FT Passengers'[Pax])

Attempts to Solve Problem

  • I've hacked at using MAX, LOOKUPVALUE, ALLSELECTED, and others.
  • Pursuant one post I found I  tried using the using the Statistics feature in Query but that button is dimmed and I can't hack at it to attempt to resolve my problem.
  • I've also tried the New Quick Measure feature in BI. The short story is, I think I'm an expert, but I'm actually a beginner.  Smiley Happy

Formulas In Attached Screen Shots (all are measures)

Passengers =
SUM('FT Passengers'[Pax])

 

Passengers max per Calendar Year =
MAXX(
    KEEPFILTERS(VALUES('Date'[Calendar Year])),
    CALCULATE([Passengers])
)

 

Pax Max Value =
CALCULATE (
MAXX ( 'FT Passengers', 'FT Passengers'[Passengers] ),
ALLSELECTED ( 'Date'[Date] )
)

 

Pax Max Value 2 =
MAXX ( 'FT Passengers', 'FT Passengers'[Passengers] )

 

Pax Max Value 3 =
LOOKUPVALUE('FT Passengers'[Pax],'Date'[Date],'FT Passengers'[Passengers])

 

 

Desired Outcome

I will create a visual. That visual could be a table or chart or other.

I want to:

  • Using a slicer, select a month (lets say January)
  • Result
    • Display the current correct value
      • This will come from my 'Passenger' measure listed above
    • Display the maximum value for (in this example) all the January for the entire time series (in my case beginning in January of the year 2001)
  • Same scenario above except instead of using a slicer for month using it for year (for example slice for year 2018).
    • Display the maximum value for all the years in the time series.

 

  • Same scenario above but with a combination of slicers.
    • e.g. I slice for Januray 2018

 

  • Same scenario above but with additional combinations of slicers
    • e.g. slice for month, year, and a region of the world.

 

Super big thank you in advance for your help and guidance.

DAXRichard

Houston, Texas

DAX  Max Value.JPGDAX Max Value Time Series2.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Show date for highest and lowest value in a time series

Hi @DAXRichArd

 

I am not sure I understand correctly what you are after. It sounds simple but because you provide a pretty long explanation I'm afraid I might be missing something. If it's what I understand, you can:

 

1. Create a calendar table to make things easier. You could use something like:

 

CalendarTable =
ADDCOLUMNS (
    FILTER ( CALENDARAUTO (); DAY ( [Date] ) = 1 );
    "Year"; YEAR ( [Date] );
    "Month"; MONTH ( [Date] );
    "Month Name"; FORMAT ( [Date]; "mmm" );
    "Day"; DAY ( [Date] )
)

or any other variant of your liking. Note I have filtered it down to include only the first day of the month since that's what your data seems to have. You could skip that and keep it all.

 

2. Create a relationship between CalendarTable[Date] and 'FT Passengers'[Date]

3. Create three simple measures:

     Passengers = SUM('FT Passengers'[Pax])

     MaxPassengers = MAX('FT Passengers'[Pax])

     MinPassengers = MIN('FT Passengers'[Pax])

4. You can now place CalendarTable[Year] or CalendarTable[Month Name] in the slicers for date or add any other slicers from the 'FT Passengers' table.

 

Bear in mind that [MaxPassengers] and [MinPassengers] as defined above will provide the maximum (minimum) value found in the rows of the 'FT Passengers'[Pax] column under the context established by your slicers. That is a value that is in a single row. I am not sure that is what you need.  I'd imagine you want some additional type of aggregation.

 

 

 

Super User
Super User

Re: Show date for highest and lowest value in a time series

@DAXRichArd

 

OK, then we need to update the [Passengers] measure we had before to ignore the year:

 

Passengers =
CALCULATE ( SUM ( 'FT Passengers'[Pax] ); ALL ( CalendarTable[Year] ) )

With that I think you should be able to build your gauges. If you need more variations on what to consider from the slicers and what not, you'd need to update the measures again. 

16 REPLIES 16
Super User
Super User

Re: Show date for lowest value in a time series

Hi @DAXRichArd

 

Could you paste your table (or a fragment thereof showing all columns) in text format? I cannot read the screen capture you've posted. 

DAXRichArd Regular Visitor
Regular Visitor

Re: Show date for lowest value in a time series

Sorry for the delay AIB. Trying to figure out how to attached a file. It'll be a moment. I think I will use a file sharing service and include the link. The link will expire in maybe 10 days. What I did was copy the table in BI (right click on table in the fields pane) then pasted it into an Excel file. DAXRichard
DAXRichArd Regular Visitor
Regular Visitor

File link: Show date for lowest value in a time series

Hi AIB, Below is the link to download the file. I'm using a service called DropSend. The free feature provides the link for 8 days. Original link removed. Use this link: http://www.filedropper.com/daxrichardpassengerdata The file is almost 5MB. When you go to the link you'll see my work email and get my full name. I'm ok with that. My plea is a plea for help so I can't be secretive. Big thank you for considering my request. DAXRichard
Super User
Super User

Re: File link: Show date for lowest value in a time series

@DAXRichArd

 

If you prefer not to share personal details you can use filedropper.com for instance. It's completely anonymous, no sign-in

DAXRichArd Regular Visitor
Regular Visitor

Re: File link: Show date for lowest value in a time series

Thx AIB! I removed the original link and used your suggestion of file dropper. Here's the link to the file: http://www.filedropper.com/daxrichardpassengerdata Again, big thank you! DAXRichard
Community Support Team
Community Support Team

Re: Show date for highest and lowest value in a time series

Hi @DAXRichArd

It is better to split problems as several small problems, so that more sufficent solutions may be offerred.

 

Best Regards

Maggie

Super User
Super User

Re: Show date for highest and lowest value in a time series

Hi @DAXRichArd

 

I am not sure I understand correctly what you are after. It sounds simple but because you provide a pretty long explanation I'm afraid I might be missing something. If it's what I understand, you can:

 

1. Create a calendar table to make things easier. You could use something like:

 

CalendarTable =
ADDCOLUMNS (
    FILTER ( CALENDARAUTO (); DAY ( [Date] ) = 1 );
    "Year"; YEAR ( [Date] );
    "Month"; MONTH ( [Date] );
    "Month Name"; FORMAT ( [Date]; "mmm" );
    "Day"; DAY ( [Date] )
)

or any other variant of your liking. Note I have filtered it down to include only the first day of the month since that's what your data seems to have. You could skip that and keep it all.

 

2. Create a relationship between CalendarTable[Date] and 'FT Passengers'[Date]

3. Create three simple measures:

     Passengers = SUM('FT Passengers'[Pax])

     MaxPassengers = MAX('FT Passengers'[Pax])

     MinPassengers = MIN('FT Passengers'[Pax])

4. You can now place CalendarTable[Year] or CalendarTable[Month Name] in the slicers for date or add any other slicers from the 'FT Passengers' table.

 

Bear in mind that [MaxPassengers] and [MinPassengers] as defined above will provide the maximum (minimum) value found in the rows of the 'FT Passengers'[Pax] column under the context established by your slicers. That is a value that is in a single row. I am not sure that is what you need.  I'd imagine you want some additional type of aggregation.

 

 

 

DAXRichArd Regular Visitor
Regular Visitor

Re: Show date for highest and lowest value in a time series

Hi AIB, Hi v-juanli-msft,

My desire is to use the minimum and maximun values in a guage visualization.

  • The minimum value will be the lowest value for the entire time series (either sliced by a single month / year or a year)
  • The maximum value will be the highest value for the entire time series (either sliced by a single month / year or a year)
  • Both the value and the target value will be the current value or current state for the reporting period (either sliced by a single month / year or a year)

My organization often wants to know or understand what is our current state relative to our worst performance / best performance.

 

AIB, thanks to both of you for your replies. AIB, I hack at your recommendation and see how I can make it work.

 

Have a great year 2019!

DAXRichard

 

DAXRichardGuage.jpg

Super User
Super User

Re: Show date for highest and lowest value in a time series

@DAXRichArd

 

Let's try to clarify. Can you give me a concrete example of how things would work? For instance, I have this and that slicer with this month and this year selected (and maybe other things) and then I would expect my measures to yield this and that result because...

I am trying to understand exactly what you need so that we can finish this up.