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
kyleldi
Frequent Visitor

Counting data between two date ranges

I have a delivery table that contains a date column and a status column.  I'm looking to graph that data by status by month and rolling week.  The data appears correctly on the Data Detail table I made (pictured), but will not graph accordingly.  I've created a table (OTD) that bins the data by month, but does not sum the status correctly by month as it should.  Being new to DAX, I'm not sure how to capture both the date range and the status in my column.  Any help would be gratefully appreciated.OTD Power BI TableOTD Power BI Table

 

 

GraphsGraphs

 

 

vw_PBI-OnTimeDelivery SQL Viewvw_PBI-OnTimeDelivery SQL View

 

 

 

 

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @kyleldi

 

What is the code you have used to create your OTD table?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark Here's my code for the OTD table.

 

OTD = DISTINCT('vw_PBI-OnTimeDelivery'[Shipped_Date (bins)])

Hi @kyleldi,

 

If you want to get the distinct count of selected date range, you can try to use below formula:

Count of Selected DateRange= 
var selectedMin=FIRSTDATE(ALLSELECTED('Table'[Date]))
var selectedMax=LASTDATE(ALLSELECTED('Table'[Date]))
return
CALCULATE(DISTINCTCOUNT('vw_PBI-OnTimeDelivery'[Shipped_Date (bins)]),FILTER(ALL('Table'),[Date]>=selectedMin&&[Date]<=selectedMax))

 

Notice: table is the source table of the date slicer.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.