Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kevboy_telford
Frequent Visitor

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 

 

1 ACCEPTED SOLUTION
Floriankx
Solution Sage
Solution 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?

View solution in original post

2 REPLIES 2
Floriankx
Solution Sage
Solution 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?

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

 

Thanks for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.