Forum Discussion
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:
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!
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])RETURNGENERATE (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)RETURNROW ("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" )))
5 Replies
Anonymous 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?
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])RETURNGENERATE (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)RETURNROW ("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" )))