cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benx
Frequent Visitor

Create a Table with current week data and previous week data

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:

Benx_0-1652464584985.png


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.

Benx_1-1652464749728.png


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!

1 ACCEPTED SOLUTION
JamesFr06
Solution Supplier
Solution Supplier

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" )
)
)

View solution in original post

5 REPLIES 5
JamesFr06
Solution Supplier
Solution Supplier

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
Frequent Visitor

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
Solution Supplier
Solution Supplier

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

JamesFr06
Solution Supplier
Solution Supplier

Hello,

 

I have created a table==> Feuil1

JamesFr06_0-1652528588262.png

and then I create a new table in Pwbi

JamesFr06_1-1652528650153.png

Try it and tell me if it works.

Benx
Frequent Visitor

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

Benx_0-1652720866393.png

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

Benx_1-1652720968202.png

Any thoughts on what's going on?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors