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
fenixen
Advocate II
Advocate II

Count items in a given datetime interval across several days

 

I want a chart that shows number of agreements per day (no problem), but i want to be able to drill down to hours and still get the correct count. 

 

I have a Calendar table connected to the fact. 

I have a Time table with 1440 minutes/24hours, not connected to the fact. 

 

Fact table

I have generated one fact row per rented day per agreement ID to be able to connect the fact table to a calendar. 

 

| Agreement ID | Date From | Time From | Date To | Time To |    Date

        1                     01.03            09:00         05.03       15:00       01.03 (1row per date in interval)

        2                     02.03            12:00         02.03       15:00         etc..

        3                     05.03             07:00        10.03       16:00

 

Base measure:      Count: =distinctcount([Agreement ID]

Then i have to somehow only count between the correct time intervall.. 

 

Using a single calculate doesn't seem to be possible.. I think i need one for the start date and one for the end date. 

 

I tried controlling which Calculate to run by using an IF statement. One measure for the start date, one for the interval and one for the end date. This works fine as long as Agreement ID is present in the visualizaton, if its not visible the IF statement will iterate on the whole data set instead of on each unique agreement ID.

 

Can i force the IF to check each row without having Agreement ID in the visualization? 

 

 

=if( max([Calender[Date])=min(Fact[DateFrom))

     then  [Measure X]

   else if( max(Calender[Date]) = max(Fact[Date To]) ; 

                 then [Measure Y]

       else [Count]

 

 

 

 

 

7 REPLIES 7
v-huizhn-msft
Employee
Employee

Hi @fenixen,

I am still confusing your requirement, could you please share more details for further analysis?

Based on my understanding, you should use the following formula to get the every day's active number of agreements.

Number of Agreement=CALCULAT(COUNTA(Fact[AgreementID]),FILTER(Fact,AND(Calendar[date]<=MIN(Fact[Date to]),Calendar[date]>=MAX(Fact[Date From]))))


In addition, what's your mean of [Measure X] and [Measure Y]?

Best Regards,
Angelia

Hi @v-huizhn-msft

 

Sorry for being unclear in my first post and thanks for the interest in my problem. 

Getting every day's active numnber of agreements is OK, but I want to account for the time of day. If the agreement ends at 15:00 it shouldn't be counted at 15:01. My current problem is that the agreements are counted for all 24 hours of the day. 

 

 

ExampleAgreement:   Starts on the 10th of april at 10:00 and ends at the 12th of april 15:00

 

The agreement should count from 10:00 the first day, then it should count for all minutes/hours of the second day (11.04) and stop counting at 15:00 the third day.  This would be easy to solve if the agreements started and ended on the same or adjacent days, but an agreement could be active for 2 weeks for instance.. 

 

If possible please have a look at the demo .pbix I've created to help understanding and hopefully solve the issue 🙂 

Dropbox download url

 

peekatdemo.jpg

 

Hi @fenixen,

I create three measures using the following.

Min = MIN('dimTime/Clock'[Hour Number])
Max = MAX('dimTime/Clock'[Next Hour])
#Measure = CALCULATE([#Agreements],filter(factReservations1rowperday,and(factReservations1rowperday[Time from]<='dimTime/Clock'[Min],factReservations1rowperday[Time to]>='dimTime/Clock'[Max])))


Then the #Measure as value level in Matrix preview, please see the screenshot.

1.PNG

Best Regards,
Angelia


Hi again @v-huizhn-msft

 

I've created a measure which returns the perfect answer given we provide the agreementID in the visualization. 

 

In the new .pbix file on the report named "New test 19.04" ive created both a matrix and graph that displays the correct number of agreements for each hour and day, seems perfect right? 

 

Problem is that you dont always want to display all the agreementID's in the visualization, the line graph looks TERRIBLE with all the ID's visible. When removing the agreementID the visualisations don't work anymore. Logical.. but i cant figure out how to fix it 😞 

 

https://www.dropbox.com/s/oe5xvz9voamr0l5/Demo.pbix?dl=0 

 

The measures 

#Agreements works if AgreementID in graph = if(	min(factReservations1rowperday[DateFrom]) = [MinDateCalendar];
									[#Agreements Startdate];
	                 			if(	MAX(factReservations1rowperday[Date To]) = [MaxDateCalendar];
										[#Agreements End date];
										[#Agreements]
									)
								)

Helping measures:
#Agreements Startdate = CALCULATE([#Agreements];FILTER(factReservations1rowperday;factReservations1rowperday[Time from]<='dimTime/Clock'[Maxtime]))

#Agreements End date = CALCULATE([#Agreements];FILTER(factReservations1rowperday;factReservations1rowperday[Time to]>'dimTime/Clock'[MinTime]))

 

 

PerfectMatrix.jpgPerfectGraph.jpg

FaultyGraph.jpg

Hi @fenixen,

Congratulations. You have resolved your issue. Thanks for your sharing solution, please mark your reply as answer, which will help others to find workaround easily.


Thanks,
Angelia

Hi @v-huizhn-msft

 

Well I wish I had solved it.. problem is that it doesn't work when i remove the AgreementID from the matrix or the graph. 

As you can see the graph looks terrible, the customer cannot use it with agreementID on the legend 😞 

 

Really annoying that I can't solve this.. I've seen so much magic in dax, I would think it should be solvable 🙂 

Hi @v-huizhn-msft

 

Your suggestion is pretty close to the solution I got but its still not getting it correct. I've created a report page in the .pbix file which uses your three measures. 

 

Issue #1 

The agreements count from 00.00 each day, even if the from period starts at 12:00 that day. 

 

Issue #2 

The measures doesn't account for the fact that a renting period could last for several days. If an agreement is active for 5 days it should count for the 3 days in the middle and for active "hours" during first and last day. 

 

In the picture below we can se the agreements resetting each days since the measure only accounts for "hour", ignoring date from and date to. 

 

Issue 2.jpg

 

 

Updated demo-file: https://www.dropbox.com/s/oe5xvz9voamr0l5/Demo.pbix?dl=0

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.