Label to mark if date has been changed in monthly snapshot

[Anonymous]

Hello all,

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

Parent	Task Name	Task Due Date	Snapshot Time	Has date been changed?
Parent 1	Task A	1/20/2020	1	no
Parent 1	Task A	1/25/2020	2	yes
Parent 1	Task A	1/25/2020	3	no
Parent 1	Task B	2/11/2020	1	no
Parent 1	Task B	2/11/2020	2	no
Parent 1	Task B	3/9/2020	3	yes
Parent 2	Task A	1/30/2020	1	no
Parent 2	Task A	1/30/2020	2	no
Parent 2	Task A	2/20/2020	3	yes

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.

[amitchandak · Accepted solution]

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

[V-pazhen-msft · Accepted solution]

@Anonymous 

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!

[amitchandak · Accepted solution]

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