Create a Table with current week data and previous week data

[Benx]

Hello all,

I have a data source table that contains a weekly snapshot of project statuses. The table udates every Tuesday morning and appends with the latest updates. Each row of data includes a field with the update date.

Here's a simplified view of the source data:

Using the source data, I woudl like to create a new table that looks something like this, so I can track and report weekly changes to key project status fields.

Does anyone have any suggestions on the best way to tackle this? I've tried a couple solutions in Power Query, and a couple using DAX to create a virtual table, but just can't make it work. Any detailed suggestions you can offer for a Power Query or DAX solution would be much appreciated!

[JamesFr06 · Accepted solution]

Hi

On the second pic you missed a ) to close the calculate function before the coma

But it does no explain everything. I have put your table name and fields names in the formula try it because here it still working well.

the new code you can copy

New Table =

VAR step1 =

SUMMARIZE (

stg_jira_product_team_initiatives_hist,

stg_jira_product_team_initiatives_hist[Initiative Key]

)

RETURN

GENERATE (

step1,

VAR issue = [Initiative Key]

VAR maxdateupdate =

CALCULATE (

MAX ( stg_jira_product_team_initiatives_hist[spark_created_date] ),

stg_jira_product_team_initiatives_hist[Initiative Key] = issue

)

VAR previousupdatedate =

CALCULATE (

MAX ( stg_jira_product_team_initiatives_hist[spark_created_date] ),

stg_jira_product_team_initiatives_hist[spark_created_date] < maxdateupdate

)

VAR curstatus =

CALCULATE (

VALUES ( stg_jira_product_team_initiatives_hist[Workflow Status] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = maxdateupdate

)

VAR prevstatus =

CALCULATE (

VALUES ( stg_jira_product_team_initiatives_hist[Workflow Status] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = previousupdatedate

)

VAR curtarget =

CALCULATE (

MAX ( stg_jira_product_team_initiatives_hist[IP Target Delivery Date] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = maxdateupdate

)

VAR prevtarget =

CALCULATE (

MAX ( stg_jira_product_team_initiatives_hist[IP Target Delivery Date] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = previousupdatedate

)

VAR curryg =

CALCULATE (

VALUES ( stg_jira_product_team_initiatives_hist[R/Y/G] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = maxdateupdate

)

VAR prevryg =

CALCULATE (

VALUES ( stg_jira_product_team_initiatives_hist[R/Y/G] ),

stg_jira_product_team_initiatives_hist[spark_created_date] = previousupdatedate

)

RETURN

ROW (

"Update Date", maxdateupdate,

"Previous Update Date", previousupdatedate,

"Currenty Status", curstatus,

"Previous Status", prevstatus,

"Status change", IF ( curstatus = prevstatus, "No", "Yes" ),

"Current Target deliver Date", curtarget,

"Previous Target deliver Date", prevtarget,

"Target change", IF ( curtarget = prevtarget, "No", "Yes" ),

"Current R/Y/G Status", curryg,

"Previous R/Y/G Status", prevryg,

"R/Y/G change", IF ( curryg = prevryg, "No", "Yes" )

)

)

[Benx]

Thanks again @JamesFr06 ! I was able to drop your new code version in, and got the expected results! You are my hero for today!

[JamesFr06]

Hi Benx, No problem happy for you. Have a nice day

[JamesFr06]

Hello,

I have created a table==> Feuil1

and then I create a new table in Pwbi

Try it and tell me if it works.

[Benx]

@JamesFr06 Thanks so much for providing this example. I really appreciate your efforts! I have tried to implement it this morning, but I'm running into a few errors:

First, it doesn't like the IF expressions for the Change columns:

When I comment those lines out, I then get a error in line 16:

Any thoughts on what's going on?