Comparing two time periods in a calculated column

[AndyOe]

I'm having an issue with date functions.

I have a table that contains Volume Forecast for a certain period of time. That period is also defined by a Start Date and an End Date and can be everything between a few days and a year.

In the dashboard there is a slicer where a single month can be selected. I created a measure (using HASONEVALUE function) to get the slicer value. The end of the month date was calculated using the EOMONTH function.

Example:

Deal_ID	Volume	Start Date	End Date	Deal Duration	Daily Volume	in August
123	5000	2018-05-15	2018-08-15	92	54,35	15
234	1000	2018-07-01	2018-12-31	183	5,46	31
345	800	2018-05-01	2018-07-31	91	8,79	0
456	3000	2018-08-01	2018-09-30	60	50,00	31
567	2000	2018-06-21	2018-08-20	60	33,33	20
678	6000	2018-08-06	2018-11-30	116	51,72	26

Let's assume I selected August 2018. What I have now are two measures Month_Start containing the value 01.08.2018 00:00:00 and Month_End containing 31.08.2018 00:00:00

I want to see the August Volume share for every deal. So the Volume should be divided by the number of days the deal lasts (realized using DATEDIFF function) multiplied with the number of days in August between Start Date and End Date. This is where I'm struggling. In the table above I already added the output I need.

My problem is that using a measure I have to work with aggregates which gives me wrong results as I need a calculation row by row. Working with a calculated column I was not able to use the slicer selection (measures Month_Start and Month_End) as part within the formula. It returned just an empty column.

Maybe there is an easy solution. As I'm still a beginner working with Power BI and DAX, any idea would be appreciated.

Thanks!!

[LivioLanzo]

Hi @AndyOe

please get my file: https://1drv.ms/u/s!AiiWkkwHZChHjxsM_NU8ZsCrGar8

You are going to need to modify slightly your data and then the measure becomes a super easy SUM as you can see from my file.

[PattemManohar · Accepted solution]

@AndyOe Please try the below as "New Column"

CountDays = COUNTROWS(FILTER(CALENDAR(DailyVolume[Start Date],DailyVolume[End Date]),month([Date]) = _MonthSelected))

Here in the filter criteria, I've used the date dimension table (Assuming that you are using your slicer from Date Dimension) and filtering the date values based on the MonthSelected.