Showing data of latest and previous date

[JulianS]

Hi everyone,

i need to display distribution data for products based on activities which were done at a specific date.

The two dates should be the actual latest date for one shop id as well as the most recent date before the latest (-1)

to generate the difference between those two.

Previously i calculated the latest distribution with the field "is last visit" for each shop id as a filter.

I think the easiest way would be to generate another column which contains a boolean value if the date is the date before the latest one.

Can anyone help me how to build a date function that accomplishes this?

My data model looks like this:

date	serial number	shop id	distribution	facings	is last visit?
01.01.2017	1	1	1	2	false
01.01.2017	2	1	1	1	false
01.01.2017	3	1	0	0	false
02.12.2017	1	1	1	1	true
02.12.2017	2	1	1	2	true
02.12.2017	4	1	1	2	true
20.12.2017	1	2	1	3	true
20.12.2017	2	2	1	5	true
20.12.2017	3	2	0	0	true
20.12.2017	4	2	0	0	true

best regards,

Julian

[v-huizhn-msft]

Hi @JulianS,

How did you calculate the "is last visit" column, and what's the rules? Based on your description, it's hard to understand your requirement well, could you please list expected result and more details for further analysis?

Best Regards,
Angelia

[JulianS · Accepted solution]

Hi @v-huizhn-msft

thanks for the reply.

I was able to resolve the issue myself by doing the following:

1. Creating a measure which calculates the date before the max date (second to last) for each of my shops :

second-to-last activity date = 
CALCULATE (
    MAX( 'activities'[Date start] );
    FILTER (
        'activities;
        [Date start]  <> MAX( ( 'activities'[Date start]  )
    )
))

2. Use this measure in a column to show the date for each shop in my shop table (unique shop id).

3. Check if the dates in my distribution result table (distribution table from first post) are dates of the second-to-last date:

Data from second-to-last date? = IF(AND(distribution result[is last visit?]=FALSE();shops[second-to-last activity date]=distribution result[date]);TRUE();FALSE())

[Oscar_Mtz_V]

Awesome!!! Superuseful, thanks!

[v-huizhn-msft]

Hi @JulianS,

Congratulations, please mark your answer as workaround, so other people can get helpful information clearly.

Thanks,
Angelia