Forum Discussion

Tomhayw's avatar
Tomhayw
Icon for Helper I rankHelper I
4 years ago
Solved

Selecting most recent data in a dataset

Hi there,

 

I currently have a dataset that updates regularly with an updated version of our CRM system as shown below:

I have set up this measure:

Pipeline state (snapshot) = calculate(sum('Pipeline (Snapshots)'[Count of pipeline projects]), DateDim[Date] = max('Pipeline (Snapshots)'[Date Obersrved]))
 
This measure takes the most recent date and visualises it.
However, as you can see the 'Monitoring' group was present on 19/08, but was not present in 22/08, but is still visualised by the measure:

Is it possible to modify my measure that would filter by the groups available on the newest date available?

 

Thanks in advance,

Tom

 

 
Need Help
  • Tomhayw's avatar
    Tomhayw
    4 years ago

    Hi there, that's what I did.

     

    I found a solution to my problem:

     

    MonthEnd = IF(CALCULATE(MAX('Pipeline (Snapshots)'[Date Observed]),FILTER('Pipeline (Snapshots)',MONTH('Pipeline (Snapshots)'[Date Observed])=MONTH(EARLIER('Pipeline (Snapshots)'[Date Observed]))))='Pipeline (Snapshots)'[Date Observed],1,0)
     
    I added this as a calculate column - it marks each entry as a 1 or 0. A 1 indicates its the latest or the last entry in each month, hence I can use this as a filter!

2 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Tomhayw ,

    Please give this a try:

    Create a Measure:

    MaxDate = MAX( 'Pipeline(Snapshots)'[Date Obersrved] )

    This should evaluate to:  22 August 2022.

     

    Then use this Measure in your measure above:

    Pipeline state (snapshot) = calculate(sum('Pipeline (Snapshots)'[Count of pipeline projects]), DateDim[Date] = [MaxDate] )

    This should then only capture the States that occur on 22 August.

     

    Regards,

    • Tomhayw's avatar
      Tomhayw
      Icon for Helper I rankHelper I
      4 years ago

      Hi there, that's what I did.

       

      I found a solution to my problem:

       

      MonthEnd = IF(CALCULATE(MAX('Pipeline (Snapshots)'[Date Observed]),FILTER('Pipeline (Snapshots)',MONTH('Pipeline (Snapshots)'[Date Observed])=MONTH(EARLIER('Pipeline (Snapshots)'[Date Observed]))))='Pipeline (Snapshots)'[Date Observed],1,0)
       
      I added this as a calculate column - it marks each entry as a 1 or 0. A 1 indicates its the latest or the last entry in each month, hence I can use this as a filter!