DAX formula - calculate # days between 2 dates

[serky]

Hi

I need some help. I have an events table that lists individual events each with a start date and end date (end date will be blank until the event has actually finished). The user selects a reporting month and I need to calculate the number of days that each event is open until the last day of the reporting month eg - if the start date was 3/11/14 and the reporting month is Dec 14, the calculation is 31/12/14 less 3/11/14.

I get the reporting month from a pivot table (only one row selected when the user picks a date from a slicer).

What is the syntax for the last day of the reporting month?
What is the formula to calculate the number of days? I have tried the following but get an error:

Days open:= if(COUNTROWS(values(dataEvents[Date Closed]))=1,
IF(isblank(values(dataEvents[Data Closed])),TODAY()-dataEvents[Start Date],2),
3)

Thanks

 

[scottsen]

You can just subtract 2 dates to get a # of days... assuming you can find the dates I'm having a hard time following the details of your model.

When the user selects a reporting month -- is that from a Calendar table, that you can just use LASTDATE(Calendar) to get the actual last day of that month?

 

[serky]

Yes - it sounds so easy but seems to be a nightmare in DAX. I would have had this completed 5 days ago in standard Excel but feel like I am completely struggling with making anything work!

I have a Calendar table. The user selects the month and year of interest. I have a measure [Date selected]=max(dimCalendar(MonthinCalendar)). This gives a result like 1/9/2014. If I change the formula to LASTDATE(dimCalendar) I get an error stating that LASTDATE must specify a column. Why doesn't this work?

I have a table dataEvents listing multiple events (one event per line). The table has numerous columns including [Start Date] and [Date Closed]. I actually need to calculate the number of days each event is open (last day of reporting month less [Start Date] - this assumes that the event does not yet have a [Date Closed].

I have tried simplifying the formula so I can debug it ie -

Days open:= if(COUNTROWS(values(dataEvents[Date Closed]))=1,
dataEvents[Date Closed],
3)

This doesn't work.

I have tried, Days open:= if(COUNTROWS(values(dataEvents[Date Closed]))=1,
2,
3)

This works ie - a pivot table of event#, date closed and days open shows a 2 for all events.

What am I doing wrong?

Thanks

 

[GDRIII]

Try adding a column to your LASTDATE formula

LASTDATE(dimCalendar[DateKey])

You have to mark your datekey column on your dimCalendar under the Design tab in 2010

As far as calculating the difference between your two dates, if they are in the same table, the easiest is to add a calculated column subtracting the two and then format that column as a whole number.

Speaking of which, sometimes to get a valid relationship between your calendar table and whatever date column in whatever table, your dates should be formatted so there isn't a time stamp.

Hope this helps. Don't give up. Once you figure it out, your mindgrapes might not stop exploding for a year or so...

GDRIII

 

[serky]

Hi

thanks for your reply but I can't use a calculated column. The calculation needs to be dynamic each reporting month because the status of an event (and therefore the days open) may change each month.

I need to allow for the following scenarios:

1. start and ends before reporting month => 0 days open
2. start before reporting month => report date less start date
3. start before and end during report month => end date less start date
4. start before and end after report month => end of report month less start date
5. start and end during report month => end date less start date
6. start during report month => end of report month less start date
7. start after report month => 0 days open

How do I calculate items such as: total days open; number of events open; average number of days open etc?

Thanks

 

[GDRIII]

Dynamic Age Calculation Using Measures « PowerPivotPro

 

[serky]

Hi GDRIII

thanks for the link - it was excellent. I do try to find info on the web but sometimes you need to know what it's called to be able to find it!

In case anyone else needs help re this, I have described my solution below but please let me know if anything should be changed/simplified.

User selected report date
[Date selected]=LASTDATE(dimCalendar[MonthInCalendar)

Calculated column for date closed (if it's blank, set a future date)
[Calc date closed]=IF(ISBLANK(dataCustomerEvents[Date Closed]),datevalue("9/99/9999"), dataCustomerEvents[Date Closed])

# customer events open (at a point in time ie - the report date)
[# Customer events open]=CALCULATE(COUNTROWS(dataCustomerEvents),
FILTER(dataCustomerEvents,
dataCustomerEvents[Date of Event]<=[Date selected]&&[Calc date closed]>=[Date selected]))

# days open for events that are still open (remove events that finished before the report date or started after the report date)
[Customer events open - days]=if(max(dataCustomerEvents[Calc date closed]<=[Date selected]||max(dataCustomerEvents[Date of event])>=[Data selected],
BLANK(),
[Date selected]-max(dataCustomerEvents[Date of event]))