cancel
Showing results for
Did you mean:
Occasional Visitor

## Comparing two time periods in a calculated column

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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Comparing two time periods in a calculated column

@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.

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

2 REPLIES 2
Super User

## Re: Comparing two time periods in a calculated column

@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.

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Super User

## Re: Comparing two time periods in a calculated column

Hi @AndyOe

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.