Forum Discussion

PatP's avatar
PatP
Frequent Visitor
8 years ago
Solved

Most recent and oldest data within a specific time range using a slicer

Hi!   I’m trying to do a Push notification report with Power BI Desktop, but I can’t get what I want.   I have two tables:   1-A table with the split of device type per month (iOS vs Android) a...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    8 years ago

    Hey PatP,

     

    How would you define last entry. If the 2 sends are done on extactly the same time and date, which one should be considered the last?

     

    If you would like to pick the maximum of them then replace the SUM with MAX in the 2 measures 

     

    Last Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MAX(Push_Sent[Date sent])))

     

    First Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MIN(Push_Sent[Date sent])))

     

     

PatP's avatar
PatP
Frequent Visitor
8 years ago

ChandeepChhabra Thank you for your help! It looks like what I'm looking for except for one thing : I would not sum the data, I would only display the last number. In my database I can get the date and time to make sure it's the very last push sent. How should we adjust your measure to show the first/last value only and not calculate the sum?

 

Thank you!

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
8 years ago

PatP Hi, adding time is a good idea

After you refresh the pbi file, It will automatically ensure that the largest date (considering time) is picked up and measure will update automatically.

 

I just added random times to the dates, here is the result

 

 

Hope it helps

  • PatP's avatar
    PatP
    Frequent Visitor
    8 years ago

    ChandeepChhabra Thanks! This look like exactly what I'm looking for. The only thing I'm worried about, is if there is two notifications sent at exactly the same time, is there any way to take just the last entry? I would like to make sure I will never display a sum of different numbers.

     

    Thank you again, I really appreciate it!

    • ChandeepChhabra's avatar
      ChandeepChhabra
      Impactful Individual
      8 years ago

      Hey PatP,

       

      How would you define last entry. If the 2 sends are done on extactly the same time and date, which one should be considered the last?

       

      If you would like to pick the maximum of them then replace the SUM with MAX in the 2 measures 

       

      Last Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MAX(Push_Sent[Date sent])))

       

      First Sent = CALCULATE(MAX(Push_Sent[App notification sends]),FILTER(Push_Sent,Push_Sent[Date sent]=MIN(Push_Sent[Date sent])))

       

       

      • PatP's avatar
        PatP
        Frequent Visitor
        8 years ago

        Thank you, this is exactly what I was looking for! 

         

        Thanks for your help!