Date Modeling to Power BI from SSAS Tabular Live Connection

[Anonymous]

Hi, I'm having an issue with some date modeling from a SSAS Tabular live connection to Power BI.

The problem is that I have a table of staff absences and I want to count monthly absences and compare with previous years.... sounds easy enough however, each absence has a start and end date (DD/MM/YYYY), which often span over multiple months.

For example:

Staff_Name      Start_Date          End_Date      No_Days_Absent

Staff A               01/02/2015          05/02/2015         5

Staff B               14/02/2015          15/02/2015         2

Staff C               12/02/2015          11/02/2016         365

....                       ....                          ....                       ....

As you will notice 'Staff C' has been recorded as long term sick so has been set as 365 days absence.

Initially, I wanted to simply group absenses by start_date so I could display a bar chart by month (x-axis) however it doesn't count the range of the dates (start_date to end_date) that exceed a month.

What would be the best approach do you think? Or how could I create a calculated table that could display every individual day in the absence period?

Thanks

Chris

[Anonymous · Accepted solution]

Thanks but I've actually just found the DAX solution from Phil_Seamark in this thread:

https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418

[v-chuncz-msft]

@Anonymous,

You may drag [Date] from a calendar table and try to use FILTER Function.

[Anonymous · Accepted solution]

Thanks but I've actually just found the DAX solution from Phil_Seamark in this thread:

https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418

[v-chuncz-msft · Accepted solution]

@Anonymous,

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.