Data Snapshot for 60 days and 90 days

[JWick1969]

Hi,

 I am hoping for some help here to be able to get and create column for 60 days snapshot and 90 days snapshot using 2 dates column (received and completed). I have a dataset called system ticket. below are sample data.

-I'm trying to convert the code from tsql to powerBI but could not figure what function to be use. Is there any other approach to get the snapshot?

case when replace(substring(convert(varchar(50),dateadd(month, 1, received),111),1,7),'/','-') =
replace(substring(convert(varchar(50),completed,111),1,7),'/','-') 
then 1.0 else 0 end FG_Snapshot_60,

Thanks in advance.

[speedramps]

We wnat to help but your decsription of the problem does not make sense. Especialy the Month ????
Please can you give a better description of your problem.

Also please provide example data in the correct datatype formats.

It took me ages to convert your text to dates like this  ....

ReceivedCompleted60day90day60_day_snapshot90_day_snapshot

08/03/2022	16/03/2022	07/05/2022	06/06/2022	Yes	Yes
03/03/2022	11/03/2022	02/05/2022	01/06/2022	Yes	Yes
05/01/2022	14/03/2022	06/03/2022	05/04/2022	No	Yes
22/02/2022	01/03/2022	23/04/2022	23/05/2022	Yes	Yes
07/01/2022	14/03/2022	08/03/2022	07/04/2022	No	Yes
03/03/2022	15/03/2022	02/05/2022	01/06/2022	Yes	Yes
07/03/2022	18/03/2022	06/05/2022	05/06/2022	Yes	Yes
20/01/2022	03/02/2022	21/03/2022	20/04/2022	Yes	Yes
08/03/2022	17/03/2022	07/05/2022	06/06/2022	Yes	Yes
02/03/2022	10/03/2022	01/05/2022	31/05/2022	Yes	Yes
04/02/2022	15/02/2022	05/04/2022	05/05/2022	Yes	Yes
16/02/2022	25/02/2022	17/04/2022	17/05/2022	Yes	Yes
14/01/2022	22/02/2022	15/03/2022	14/04/2022	Yes	Yes
08/03/2022	15/03/2022	07/05/2022	06/06/2022	Yes	Yes
08/03/2022	16/03/2022	07/05/2022	06/06/2022	Yes	Yes

[speedramps]

Hi JWick1969

I want to help but I dont understand your description. ☹️

please provide example input data (in a table format not screen shots so we ca import it and build solution)

example desired output data

a clear description (not example of logic that does not work)

Thanks  😎

[JWick1969]

Thanks @speedramps. my code below gives me the desired result but I dont want to use the days instead month.

I want to consider this scenario, this records does not identified for 60 days snapshop because 60days snapshot is  greater than the completed date with difference of  7 days and i want it to consider using the month to capture for 60 days snapshot.

received-------------completed ------60 days snapshot_date

1/5/2022 4:17

3/14/2022

3/6/2022 4:17

Any idea on how to use month.

Snapshot 60  = IF([completed] >= [received] && [completed] <= [received]+60, "Yes","No")

Snapshot 90 = IF([completed] >= [received] && [completed] <= [received]+90, "Yes","No")

received	completed	60_day_snapshot_date	90_day_snapshot_date	60_day_snapshot	90_day_snapshot
3/8/2022 7:38	3/16/2022	5/7/2022 7:38	6/6/2022 7:38	Yes	Yes
3/3/2022 10:52	3/11/2022	5/2/2022 10:52	6/1/2022 10:52	Yes	Yes
1/5/2022 4:17	3/14/2022	3/6/2022 4:17	4/5/2022 4:17	No	Yes
2/22/2022 9:48	3/1/2022	4/23/2022 9:48	5/23/2022 9:48	Yes	Yes
1/7/2022 2:08	3/14/2022	3/8/2022 2:08	4/7/2022 2:08	No	Yes
3/3/2022 10:52	3/15/2022	5/2/2022 10:52	6/1/2022 10:52	Yes	Yes
3/7/2022 7:35	3/18/2022	5/6/2022 7:35	6/5/2022 7:35	Yes	Yes
1/20/2022 0:07	2/3/2022	3/21/2022 0:07	4/20/2022 0:07	Yes	Yes
3/8/2022 7:37	3/17/2022	5/7/2022 7:37	6/6/2022 7:37	Yes	Yes
3/2/2022 5:09	3/10/2022	5/1/2022 5:09	5/31/2022 5:09	Yes	Yes
2/4/2022 11:45	2/15/2022	4/5/2022 11:45	5/5/2022 11:45	Yes	Yes
2/16/2022 3:19	2/25/2022	4/17/2022 3:19	5/17/2022 3:19	Yes	Yes
1/14/2022 1:07	2/22/2022	3/15/2022 1:07	4/14/2022 1:07	Yes	Yes
3/8/2022 3:32	3/15/2022	5/7/2022 3:32	6/6/2022 3:32	Yes	Yes
3/8/2022 7:38	3/16/2022	5/7/2022 7:38	6/6/2022 7:38	Yes	Yes

[v-rzhou-msft]

Hi @JWick1969 ,

i want it to consider using the month to capture for 60 days snapshot.

received-------------completed ------60 days snapshot_date

1/5/2022 4:17

3/14/2022

3/6/2022 4:17

Do you want this [60_day_snapshot] show by month? So the result is "Yes" instead of "No".

Try measures below.

Snapshot 60 = 
VAR _YEARMONTH_received =
    YEAR ( 'Table'[received] ) * 100
        + MONTH ( 'Table'[received] )
VAR _YEARMONTH_COMPLETED =
    YEAR ( 'Table'[completed] ) * 100
        + MONTH ( 'Table'[completed] )
VAR _YEARMONTH_Snapshot60 =
    YEAR ( 'Table'[60_day_snapshot_date] ) * 100
        + MONTH ( 'Table'[60_day_snapshot_date] )
RETURN
    IF (
        _YEARMONTH_COMPLETED >= _YEARMONTH_received
            && _YEARMONTH_COMPLETED <= _YEARMONTH_Snapshot60,
        "Yes",
        "No"
    )

Snapshot 90 = 
VAR _YEARMONTH_received =
    YEAR ( 'Table'[received] ) * 100
        + MONTH ( 'Table'[received] )
VAR _YEARMONTH_COMPLETED =
    YEAR ( 'Table'[completed] ) * 100
        + MONTH ( 'Table'[completed] )
VAR _YEARMONTH_Snapshot90 =
    YEAR ( 'Table'[90_day_snapshot_date] ) * 100
        + MONTH ( 'Table'[90_day_snapshot_date] )
RETURN
    IF (
        _YEARMONTH_COMPLETED >= _YEARMONTH_received
            && _YEARMONTH_COMPLETED <= _YEARMONTH_Snapshot90,
        "Yes",
        "No"
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JWick1969]

Hi @v-rzhou-msft, Thank you for the reply.

What if i dont have a column for 60 and 90 days snapshot date instead i will use the received and completed column. what looks like the code to achieved by month?

Also the result should be 1 or 0, meaning if the fg date fall on 60 days snapshot will have an equivalent value of 1 else 0.

[v-rzhou-msft · Accepted solution]

Hi @JWick1969 ,

If you don't want to add columns for 60 and 90 days snapshot date, you can calcualte 60 or 90 days snapshot date in code by VAR function.

Snapshot 60 = 
VAR _SNAPSHOT_60 = 'Table'[received] + 60
VAR _YEARMONTH_received =
    YEAR ( 'Table'[received] ) * 100
        + MONTH ( 'Table'[received] )
VAR _YEARMONTH_COMPLETED =
    YEAR ( 'Table'[completed] ) * 100
        + MONTH ( 'Table'[completed] )
VAR _YEARMONTH_Snapshot60 =
    YEAR ( _SNAPSHOT_60 ) * 100
        + MONTH ( _SNAPSHOT_60)
RETURN
    IF (
        _YEARMONTH_COMPLETED >= _YEARMONTH_received
            && _YEARMONTH_COMPLETED <= _YEARMONTH_Snapshot60,
        1,
        0
    )

Snapshot 90 = 
VAR _SNAPSHOT_90 = 'Table'[received] + 90
VAR _YEARMONTH_received =
    YEAR ( 'Table'[received] ) * 100
        + MONTH ( 'Table'[received] )
VAR _YEARMONTH_COMPLETED =
    YEAR ( 'Table'[completed] ) * 100
        + MONTH ( 'Table'[completed] )
VAR _YEARMONTH_Snapshot90 =
    YEAR (_SNAPSHOT_90 ) * 100
        + MONTH (_SNAPSHOT_90)
RETURN
    IF (
        _YEARMONTH_COMPLETED >= _YEARMONTH_received
            && _YEARMONTH_COMPLETED <= _YEARMONTH_Snapshot90,
        1,
        0
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.