cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Label to mark if date has been changed in monthly snapshot

Hello all,

 

I have a dataset that currently looks like this, minus the "Has date been changed?" column:

ParentTask NameTask Due DateSnapshot TimeHas date been changed?
Parent 1Task A1/20/20201no
Parent 1Task A1/25/20202yes
Parent 1Task A1/25/20203no
Parent 1Task B2/11/20201no
Parent 1Task B2/11/20202no
Parent 1Task B3/9/20203yes
Parent 2Task A1/30/20201no
Parent 2Task A1/30/20202no
Parent 2Task A2/20/20203yes

 

My dataset consists of a hierachy of tasks that fall under a parent. Task A shows up in Parent 1 and Parent 2 but it's not the same task. Every month there is a snapshot taken showing snapshots number 1,2,3..
I want to have a column that labels each task for each snapshot as "Yes" or "No" checking if the "Task Due Date" has changed. For example Task A from Parent 1 has 1/20/2020  for snapshot 1 so there should be "No" by default. For snapshot 2, due date is 1/25/2020 so it's "Yes", then for snapshot 3 it's 1/25/2020 so it's again "No". This needs to be different from Task A that belongs to Parent 2 however.

 

Any suggestions would be helpful, thank you.

2 ACCEPTED SOLUTIONS
Super User IV
Super User IV

Create two column. "Column is the result you want"

 

Past Date = MAXX(FILTER(DateChanges,DateChanges[Parent]=EARLIER(DateChanges[Parent]) && DateChanges[Task Name]=EARLIER(DateChanges[Task Name])&& DateChanges[Snapshot Time]<EARLIER(DateChanges[Snapshot Time])),DateChanges[Task Due Date])
Column = if(ISBLANK(DateChanges[Past Date]),"No",if(DateChanges[Task Due Date]=DateChanges[Past Date],"No","Yes"))

 

Link

https://www.dropbox.com/s/b04xl7ow21lf63g/DateChanges.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Community Support
Community Support

@bogdans 

I was able to get your expected outcome with the following column: 

 

Datechanged? =
VAR previousdate =
    CALCULATE ( MAX ( 'Table'[Task Due Date] ),
        FILTER ( 'Table',
                 'Table'[Parent] = EARLIER ( 'Table'[Parent] )
                 && 'Table'[Task Name] = EARLIER ( 'Table'[Task Name] )
                 && 'Table'[Snapshot Time] = EARLIER ( 'Table'[Snapshot Time] ) - 1))
RETURN
    IF ( previousdate = [Task Due Date] || ISBLANK ( previousdate ), "no", "yes" )

 

 
Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

View solution in original post

2 REPLIES 2
Community Support
Community Support

@bogdans 

I was able to get your expected outcome with the following column: 

 

Datechanged? =
VAR previousdate =
    CALCULATE ( MAX ( 'Table'[Task Due Date] ),
        FILTER ( 'Table',
                 'Table'[Parent] = EARLIER ( 'Table'[Parent] )
                 && 'Table'[Task Name] = EARLIER ( 'Table'[Task Name] )
                 && 'Table'[Snapshot Time] = EARLIER ( 'Table'[Snapshot Time] ) - 1))
RETURN
    IF ( previousdate = [Task Due Date] || ISBLANK ( previousdate ), "no", "yes" )

 

 
Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

View solution in original post

Super User IV
Super User IV

Create two column. "Column is the result you want"

 

Past Date = MAXX(FILTER(DateChanges,DateChanges[Parent]=EARLIER(DateChanges[Parent]) && DateChanges[Task Name]=EARLIER(DateChanges[Task Name])&& DateChanges[Snapshot Time]<EARLIER(DateChanges[Snapshot Time])),DateChanges[Task Due Date])
Column = if(ISBLANK(DateChanges[Past Date]),"No",if(DateChanges[Task Due Date]=DateChanges[Past Date],"No","Yes"))

 

Link

https://www.dropbox.com/s/b04xl7ow21lf63g/DateChanges.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors