Forum Discussion

Kevboy_telford's avatar
Kevboy_telford
Frequent Visitor
8 years ago
Solved

Show min date data

I want a user to be able to select a date range and Power BI to show the data for the first date and last date only  

I would try using summarize but I need to be able to calculate on the fly....

 

I have the following measures

 

earliestdate = min('Calendar'[Date])

latestdate = max('Calendar'[Date])

 

FirstDateCount = CALCULATE(DISTINCTCOUNT('Customer_Journey'[customerid]),FILTER('JM_Insight Calendar','JM_Insight Calendar'[Date]<=[earliestdate]))

LastDateCount = CALCULATE(DISTINCTCOUNT('Customer_Journey'[customerid]),FILTER('JM_Insight Calendar','JM_Insight Calendar'[Date]>=[latestdate]))

 

however both FirstDateCount and LastDateCount are giving me the same answer...

 

equivalent in SQL that I am trying to do is:

 

DECLARE @customerselectedDate_first DATE = '20180101' 
DECLARE @customerselectedDate_last  DATE = '20180430' 

SELECT current_status, 
       Count(DISTINCT customerid) AS customers 
FROM   customer_journey 
WHERE  dates = @customerselectedDate_first 
GROUP  BY current_status 

SELECT current_status, 
       Count(DISTINCT customerid) AS customers 
FROM   customer_journey 
WHERE  dates = @customerselectedDate_last 
GROUP  BY current_status 

 

  • Hello you can dim your variable in the formula:

     

    FirstDateCount = 
    VAR EarlieastDate = MIN('Calendar'[Date])
    RETURN
    CALCULATE(DISTINCTCOUNT('Customer_Journey'[customerid]),
    FILTER('JM_Insight Calendar','JM_Insight Calendar'[Date]=[EarliestDate]))

    Are Customer_Journey and JM_Insight Caledar related?

2 Replies

  • Hello you can dim your variable in the formula:

     

    FirstDateCount = 
    VAR EarlieastDate = MIN('Calendar'[Date])
    RETURN
    CALCULATE(DISTINCTCOUNT('Customer_Journey'[customerid]),
    FILTER('JM_Insight Calendar','JM_Insight Calendar'[Date]=[EarliestDate]))

    Are Customer_Journey and JM_Insight Caledar related?

    • Kevboy_telford's avatar
      Kevboy_telford
      Frequent Visitor

      That is weird - it works when I have it as a VAR within the measure but not when its separate

       

      Thanks for your help!