Forum Discussion
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
- FloriankxSolution Sage
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_telfordFrequent 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!