Show date for highest and lowest value in a time series

[DAXRichArd]

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.  

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

[AlB · Accepted solution]

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.

[arunlaksh04]

I am having hourly interval values summarized to MAX and MIN for a Month. But i need the MAX Date& Time and MIN Date and Time

[arunlaksh04]

how to fetch the MAX value along with time series data

in Power BI

[v-juanli-msft]

Hi @DAXRichArd

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

Best Regards

Maggie

[AlB · Accepted solution]

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]

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

[AlB]

@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.  

[DAXRichArd]

Sorry for the inconvinience and again thank you for your follow through. I can't say thank you enough. The dashboard I want to create will look something like the attached image.

[AlB]

@DAXRichArd

OK so let's say we have in the slicer the following:

Month: February

Year: 2015

Airport: HOU

Region: USA

What would be the result for each of the three measures you want? Explain conceptually how it would be calculated so that I can understand if I missed something earlier? 

[DAXRichArd]

Hi AIB,

Month: February

Year: 2015

Airport: HOU

Region: USA

My gauges would show:

• The lowest reading for the entire data set for the month of February (this is the lower boundary (minimum value)  of the guage)
  • For HOU Airport
  • For the region USA
• The highest reading for the entire data set for the month of February (this is the upper boundary (maximum value) of the guage)
  • For HOU Airport
  • For the region USA
• The current reading for February 2015
  • For HOU Airport
  • For the region USA

Thx

DAXRichard

[AlB]

@DAXRichArd

Ok, so for the min and max we would be looking at the month February of all years in your data. Correct? 

[DAXRichArd]

Good morning,

Sorry if this is a repeat reply (had internet connectivity problems from home this moring).

Your question:

Ok, so for the min and max we would be looking at the month February of all years in your data. Correct?

Response:

Yes, that is correct.

DAXRichard

[AlB · Accepted solution]

@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. 

[DAXRichArd]

Ahhh.... I'm my own  worst enemy. I was explecting something complicated. I guess I was over engineering it. Big thanks AIB. I'll give it a try hopefully sometime today.

DAXRichard

[AlB]

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]

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

[AlB]

@DAXRichArd

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

[DAXRichArd]

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

[DAXRichArd]

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