Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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())
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
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())
Awesome!!! Superuseful, thanks!
Hi @JulianS,
Congratulations, please mark your answer as workaround, so other people can get helpful information clearly.
Thanks,
Angelia
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |