cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyOe Occasional Visitor
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_IDVolumeStart DateEnd DateDeal DurationDaily Volumein August
12350002018-05-152018-08-159254,3515
23410002018-07-012018-12-311835,4631
3458002018-05-012018-07-31918,790
45630002018-08-012018-09-306050,0031
56720002018-06-212018-08-206033,3320
67860002018-08-062018-11-3011651,7226

 

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

image.png

 

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

image.png

 

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
Super User

Re: Comparing two time periods in a calculated column

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.

 


 


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


Proud to be a Datanaut!