cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bhavyapj Frequent Visitor
Frequent Visitor

Find Latest date

Hi All,

 

The data that I have has all the surverys that were done to all the stores.

I want to consider only the latest visit to a store for that month. If the store was not visited on that month, then consider the last visited data irrespective of the month. Something like below

 

Store Number   July        Aug

1001                  12-06     25-07

1002                  22-06     22-06

 

In the above example, since Store 1002 was not visited in Aug, it should show me the last visit date. I'm using the below example, this shows blank instead of 22-06

 

Lastvisit = CALCULATE(LASTDATE(FACT_DETAILS_KPIS[session_date]),ALLEXCEPT(FACT_DETAILS_KPIS,FACT_DETAILS_KPIS[store_number],DIM_DATE[fiscalmonth]))
 
Thanks,
Bhavya

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Find Latest date

Hi Bhavya,

 

Since you have a Date table, it could be a simple measure like below. Please download the demo from the attachment.

Measure =
CALCULATE (
    MAX ( FACT_DETAILS_KPIS[Visit Date] ),
    FILTER ( ALL ( DIM_DATE ), DIM_DATE[Date] <= MAX ( DIM_DATE[Date] ) )
)

Find-Latest-date

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Find Latest date

Hello - are you able to share your dataset?? thanks

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: Find Latest date

Hi Bhavya,

 

Since you have a Date table, it could be a simple measure like below. Please download the demo from the attachment.

Measure =
CALCULATE (
    MAX ( FACT_DETAILS_KPIS[Visit Date] ),
    FILTER ( ALL ( DIM_DATE ), DIM_DATE[Date] <= MAX ( DIM_DATE[Date] ) )
)

Find-Latest-date

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bhavyapj Frequent Visitor
Frequent Visitor

Re: Find Latest date

Hi Dale,

 

Thank You so much. This is very helpful. 

I also need to apply this in my KPI calculation. I need to find difference between distribution Sku every month vs July. If Distribution sku for a particular month doesn't exist( like Aug in the below example), then we need to consider the last distribution sku(in this case- July) and then find the difference.

 

PBI Screenshot.PNG

Any help will be much appreciated.

 

Thank You,

Bhavya

Community Support Team
Community Support Team

Re: Find Latest date

Hi @Bhavyapj,

 

It seems the old question. Can you share a dummy sample that is similar to your real data? The main idea is to find the last date and then sum up the sku value where the date equals the last date. 

 

Best Regards,

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