How to get the last update within a date range

[Anonymous]

Hi guys!

I'm struggling with something that seems to be pretty easy. I have a model consisting of 2 tables: projects and updates. I have created a calculated table for Dates too, but it isn't connected to the other tables. Here the detail of the tables:

• Projects: It gathers all the features of the projects.
  • Key: Unique identifier of any project.
  • Name: Name of the project.
  • Created: Date when the project was registered.
  • LastPhase: Phase of the project as of today. Options: "Exploratory", "POC", "MVP", "Deployment".
  • LastStatus: Status of the project as of today. Options: "Continue and progressing as expected", "Continue but critical assessment needed", "Stopped" and "Completed".
  • Description: Brief description of the project.
• Updates: It is a transaction table, with all the updates registered by project over time.
  • Key: Unique identifier of any project.
  • Created: Date when the project was updated.
  • Phase: New phase of the project.
  • Status: New status of the project.
  • Comments: Comments related to the update.
  • Next steps: Next steps related to the update.
• Date: Date table containing dates between min and max of Updates[Created].

Date = 
ADDCOLUMNS(
    CALENDAR(
        DATE(YEAR(MIN('Updates'[Created])),1,1),
        DATE(YEAR(MAX('Updates'[Created])),12,31)
    ),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "mmmm"),
    "Month Number", MONTH([Date])
)  

Active relationship is Projects[Key] 1:* Updates[Key].

My goal is to display two things:

1. A scatter plot with "Current status" in the Axis X and the "distinct count of projects" in Size. However, I want to be able to consider a date range for obtaining the "Current status". I mean, if I select a period in Date[Day] (i.e. from May 1st to June 2nd) I want to get the status of every existing project as of June 2nd and show the result of aggregating each project in the scatter plot, in function of the Current status.
2. A detail table with the following fields: Projects[Key], Projects[Name], Projects[Description], [Current phase], [Current status], [Current comments] and [Current next steps]. The fields starting with "Current" should be calculated measures corresponding to the phase, status, comments and next steps for the selected period (in the previous example, as of June 2nd).

I would like that both visuals were connected so when I select a bubble of the scatter plot, the table only displays the projects with the corresponding "Current status".

Thank you in advance.

Here is the PBIX: click here.

Best regards,

Fernando

[Anonymous]

Thank you so much @v-deddai1-msft!

Best regards,

Fernando

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please update the measure1 to 

Measure1 = 
VAR a =
    SUMMARIZE (
        'Updates',
        'Updates'[Key],
        "lastdate",
            CALCULATE (
                MAX('Updates'[Created]),
                FILTER ( 'Updates', 'Updates'[Created] <= MAX('Date'[Date] ))
            )
    )
VAR b =
ADDCOLUMNS (
        a,
        "laststatus",
            CALCULATE (
                MAX ( 'Updates'[Status] ),
                FILTER ( ALL('Updates'), 'Updates'[Created] = EARLIER ( [lastdate] )&&'Updates'[Key] = EARLIER('Updates'[Key]) )
            ),
        "lastphase",
            CALCULATE (
                MAX ( 'Updates'[Phase] ),
                FILTER (ALL('Updates'), 'Updates'[Created] = EARLIER ( [lastdate] )&&'Updates'[Key] = EARLIER('Updates'[Key]) )
            ),
        "lastcomments",
            CALCULATE (
                MAX ( 'Updates'[Comments] ),
                FILTER ( ALL('Updates'), 'Updates'[Created] = EARLIER ( [lastdate] )&&'Updates'[Key] = EARLIER('Updates'[Key]) )
            ),
        "last next steps",
            CALCULATE (
                MAX ( 'Updates'[Next steps] ),
                FILTER ( ALL('Updates'), 'Updates'[Created] = EARLIER ( [lastdate] )&&'Updates'[Key] = EARLIER('Updates'[Key]) )
            )
    )
RETURN
   CALCULATE(DISTINCTCOUNT('Updates'[Key]),FILTER(b,[lastphase] = MAX('Phases'[Phase])))

Then use the 'Phases'[Phase] as x-axis in scatter plot, then you can get what you need.

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ed_t1idp5TVPgelkiB5mmhQBL5fz8B5T4Vm-6cxjM2Qqdg?e=3pYdNw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft!

Thanks for your help :). I've checked and it's close to what I was looking for. The table is showing the latest status, phase, comments and next steps for each project. However, the scatter plot is not working properly since it should show the aggregation of the table (only one count per project, that is, maximum of 5 as result of summing all the counts by phase, since there are 5 projects). For example, as of May 8th 2020 there should be 2 projects in POC and 1 in deployment. How could I get this result in the scatter plot? Here the latest PBIX.

Thank you in advance.

Best regards,

Fernando

[v-deddai1-msft]

Hi @Anonymous ,

I have edit the reply, please try again.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Anonymous ,

Would you please refer to the following measure:

measure for scatter plot:

Measure1 =
VAR a =
    SUMMARIZE (
        'Updates',
        'Updates'[Key],
        "lastdate",
            CALCULATE (
                LASTNONBLANK ( 'Updates'[Created], 1 ),
                FILTER ( 'Updates', 'Updates'[Created] IN VALUES ( 'Date'[Date] ) )
            )
    )
VAR b =
    ADDCOLUMNS (
        a,
        "laststatus",
            CALCULATE (
                MAX ( 'Updates'[Status] ),
                FILTER ( 'Updates', 'Updates'[Created] = EARLIER ( [lastdate] ) )
            )
    )
RETURN
    COUNTX (
        FILTER ( b, [laststatus] = MAX ( 'Updates'[Status] ) ),
        'Updates'[Key]
    )

As other measures are quite long, I suggest you refer to PBIX directly: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQHPBZZB0ehHvQdCvo76TPwBJgr5PX7_kN5qvX13l3MrIg?e=3FYPAz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon!

Thank you for your reply.

I've download the PBIX to see the measures, but it seems to be the original PBIX with only the star schema. May you share the wrong PBIX?

Thank you in advance.

Best regards,

Fernando