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

Data Snapshot for 60 days and 90 days

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,

 

JWick1969_0-1652698153073.png

 

Thanks in advance.

 

 

1 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.

RicoZhou_0-1653473356253.png

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.

 

 

View solution in original post

6 REPLIES 6
speedramps
Super User
Super User

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
Super User
Super User

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  😎

 

 

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:173/14/20223/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_date90_day_snapshot_date60_day_snapshot90_day_snapshot
3/8/2022 7:383/16/20225/7/2022 7:386/6/2022 7:38YesYes
3/3/2022 10:523/11/20225/2/2022 10:526/1/2022 10:52YesYes
1/5/2022 4:173/14/20223/6/2022 4:174/5/2022 4:17NoYes
2/22/2022 9:483/1/20224/23/2022 9:485/23/2022 9:48YesYes
1/7/2022 2:083/14/20223/8/2022 2:084/7/2022 2:08NoYes
3/3/2022 10:523/15/20225/2/2022 10:526/1/2022 10:52YesYes
3/7/2022 7:353/18/20225/6/2022 7:356/5/2022 7:35YesYes
1/20/2022 0:072/3/20223/21/2022 0:074/20/2022 0:07YesYes
3/8/2022 7:373/17/20225/7/2022 7:376/6/2022 7:37YesYes
3/2/2022 5:093/10/20225/1/2022 5:095/31/2022 5:09YesYes
2/4/2022 11:452/15/20224/5/2022 11:455/5/2022 11:45YesYes
2/16/2022 3:192/25/20224/17/2022 3:195/17/2022 3:19YesYes
1/14/2022 1:072/22/20223/15/2022 1:074/14/2022 1:07YesYes
3/8/2022 3:323/15/20225/7/2022 3:326/6/2022 3:32YesYes
3/8/2022 7:383/16/20225/7/2022 7:386/6/2022 7:38YesYes

 

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.

RicoZhou_0-1652949206384.png

 

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.

 

Hi @RicoZhou, 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.

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.

RicoZhou_0-1653473356253.png

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.

 

 

Helpful resources

Announcements
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!

Top Solution Authors
Top Kudoed Authors