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

How to get sum of units in user-defined date period?

Hi, you guys have helped me out before, hoping you can do so again! 😊

I have a table that contains the following fields:

 

ClientID

Supplier

StartDate

EndDate

WeeklyUnits

1

ACMECo

06/06/2019

 

10

2

BillyOnions

01/01/2019

17/01/2019

5

3

FredBloggs

01/02/2019

03/03/2019

10

4

ACMECo

31/12/2018

12/05/2019

20

 

I have a DateKey table where I have NOT created a relationship to any of the date fields in other tables.

 

I use a date slicer in the report to produce a graph of units per week by supplier. This is used for giving a trend on what a provider’s planned unit capacity was at the beginning of each week. To get this I use the following measure in conjunction with filtering the visual to just show data for Mondays:

 WeeklyUnitSnapshot=
CALCULATE(
  SUM('MyTable'[Weekly Units]),
   FILTER( 'MyTable', [START DATE] <= FIRSTDATE(DateKey[Date])),
   FILTER( 'MyTable', [END DATE] = BLANK() || 'MyTable'[END DATE] >= LASTDATE(DateKey[Date]))
   )

So far, so good, but I also need to track what was actually delivered by suppliers in the date period defined by the report user. eg. "How many units did AcmeCo provide between 1st of Jan and 1st of Feb?"

 

What I need to achieve is calculate the number of days the order was open within the user selected dates, taking into account the start and end dates in the table, then multiplying the number of days by weekly units and then dividing by 7.

 

I’ve tried various ideas but can’t fathom how to get this working, I tried another measure but it was ignoring the user-selected date range and just using the first and last dates in my DateKey table.

 

Any help would be greatly appreciated!

6 REPLIES 6
Greg_Deckler
Super User
Super User


Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

These both look very interesting, I confess to being a bit out of my depth here! 🙂

 

In the period billing example, there is a fixed monthly cost, but I need to calculate number of units, it's that further step I can't get.

 

The idea I have in mind is that the user will select a date range using a slicer to select dates between (these could be any number of days, months etc, so totally user-defined). Then I need the measure to calculate how many days the ticket was open for, multiply weekly units by number of days and then divide this by by 7 to return an actual number of supplied units. Does that make sense?

 

Really appreciate any help you can give me!

 

 

Hi, still trying to do something based on the suggestions above, but going hopelessly awry. I know I need to be doing something with variables and datediff, but that's as far as I can get.

 

Do anyone have any further guidance on this?

Anonymous
Not applicable

-- First of all, change the measure to this:

WeeklyUnitSnapshot =
var __firstDate = FIRSTDATE ( DateKey[Date] )
var __lastDate = LASTDATE( DateKey[Date] )
return
	CALCULATE (
	    SUM ( 'MyTable'[WeeklyUnits] ),
	    'MyTable'[StartDate] <= __firstDate,
	    OR(
	    	'MyTable'[EndDate] = BLANK (),
	     	'MyTable'[EndDate] >= __lastDate
	    )
	)

-- The main rule of DAX says: Don't filter a table
-- if you can filter a column. Please try to stick
-- to it unless you want to see problems with calculation
-- speed. Please also hide the StartDate and EndDate
-- fields in the fact table. You most likely don't need
-- to expose them. To make the model better and easier
-- please get rid of the BLANKs in the EndDate column
-- replacing them with some date far away into the future,
-- say, 9999-01-01. If you do this, you'll be able to
-- rewrite the above measure as

WeeklyUnitSnapshot =
var __firstDate = FIRSTDATE ( DateKey[Date] )
var __lastDate = LASTDATE( DateKey[Date] )
return
	CALCULATE (
	    SUM ( 'MyTable'[WeeklyUnits] ),
	    'MyTable'[StartDate] <= __firstDate,
	    'MyTable'[EndDate] >= __lastDate
	)

-- Now, for the other measure... Let's say you
-- select a date period from the DateKey table,
-- for instance, a month. I understand ClientID is
-- also OrderID. Please adjust the conditions to
-- suit your needs.

[Weekly Units Equivalent] =
var __startDatesSelection = FIRSTDATE( DateKey[Date] )
var __endDateSelection = LASTDATE( DateKey[Date] )
var __weeklyUnitsCalculation =
SUMX(
	GENERATE(
		MyTable,
		var __orderWeeklyUnits = MyTable[WeeklyUnits]
		var __startDate = MyTable[StartDate]
		var __endDate =
			if(
				ISBLANK(MyTable[EndDate]),
				DATE(9999,1,1),
				MyTable[EndDate]
			)
		var __numOfDaysOrderOpen =
			if(
				and(
					__startDate >= __startDateSelection,
					__endDate <= __endDateSelection
				),
				DATEDIFF( __startDate, __endDate, DAY) + 1
			)
		return
			row(
				"WU Equivalent",
				__numOfDaysOrderOpen * __orderWeeklyUnits / 7
			)
	),
	[WU Equivalent]
)
return
	__weeklyUnitsCalculation

Best

Darek

Thank you so much for this!

 

It makes sense and I understand the logic of what you've done, however it appears to be returning the wrong result - a much lower figure than it should be.

 

I'm trying to understand which factors could be affecting the result.

 

Other measures that I use to count and sum totals appear to be correct, but something is going wrong with this one. Do you have any suggestions as to where to look for where this is going wrong?

 

Thank you so much for your help!

Anonymous
Not applicable

You have to really check if the logic is correct. Especially the inequalities.

 

Best

Darek

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.

Top Solution Authors