cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JulianS Regular Visitor
Regular Visitor

Showing data of latest and previous date

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:

dateserial numbershop iddistributionfacingsis last visit?
01.01.20171112false
01.01.20172111false
01.01.20173100false
02.12.20171111true
02.12.20172112true
02.12.20174112true
20.12.20171213true
20.12.20172215true
20.12.20173200true
20.12.20174200true

 

 

best regards,

 

Julian

1 ACCEPTED SOLUTION

Accepted Solutions
JulianS Regular Visitor
Regular Visitor

Re: Showing data of latest and previous date

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())

 

View solution in original post

4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Showing data of latest and previous date

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 Regular Visitor
Regular Visitor

Re: Showing data of latest and previous date

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())

 

View solution in original post

v-huizhn-msft Super Contributor
Super Contributor

Re: Showing data of latest and previous date

Hi @JulianS,

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

Thanks,
Angelia

Highlighted
Oscar_Mtz_V Frequent Visitor
Frequent Visitor

Re: Showing data of latest and previous date

Awesome!!! Superuseful, thanks!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 247 members 2,350 guests
Please welcome our newest community members: