No of days in a range grouped by Year

DAX DAX Calculations
1

Can you help with this please? Have been trying to work it out all day.

I have a start date and an end date and need to show the number of days split by years.

For example hire start date is 01/11/15 and hire end date is 01/03/17.

I have this data in one table and have also created a calendar table. The date column in the calendar table is linked to the end date. It currently lumps the days in the end hire date year. I know why it does this but trying to work what I need to do to separate the no days in to 2015, 2016 and 2017.

Thinking I might need to create a virtual table with dates, but cant work it out.

Can you please help?
Thanks
Mel

2

I’ve just realised this is called events in progress, so will search on here for the info.

3

Hi @meldon

There was a challenge posted that seems to be similar to your scenario using Power Query, hopefully some of the responses may help you:

Power Query Challenge - Repeat Rows for Courses across Years - Query Editor - Enterprise DNA Forum

This will get the dates within each year the employee hits. From there it’ll be a simple date diff and sum it up if required.

Thanks,

2 Likes
4

Thanks that looks like it would work. I think I would like a solution in DAX though as I would like to be able to group by year or by quarter or month. Only other thing with the power query solution it would increase the number of rows greatly, not sure if it would be an issue though.

5

I’ve got a bit of a diagram of what I need. I can get the top part ok. But am looking for the no of days in the year or month, like the bottom part I have done in excel (just plugged some numbers in).

image
image846×460 79 KB

6

Hello @meldon,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below is the DAX measure alongwith the screenshot of the final results provided -

Distribution Of Days = 
VAR _vTable = 
SUMMARIZE(
    Data , 
    Data[Course] ,
    Data[Start Date] , 
    Data[End Date] )

VAR _No_Of_Days =
COUNTROWS(
    FILTER(
        CROSSJOIN(
            CALCULATETABLE(
                VALUES( Dates[Date] ) , 
                Dates[YearOffset] <= 0 ) ,
            _vTable ) , 
        Dates[Date] >= Data[Start Date] &&
        Dates[Date] <= Data[End Date] ) )

RETURN
_No_Of_Days

Final Results
Final Results1035×393 39.1 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note: - While posting query onto the Forum please upload PBIX file for the reference purposes. Without PBIX file, it just un-necessarily wastes the time of the other Forum members who’re providing assistance onto the Forum in creating the mock-up data. For poster of the query, it could be a general question but for members who provide assistance, for them it’s not. Ensure that either working or demo PBIX file is attached so that members can provide assistance in a better and efficient manner.

Thanks and Warm Regards,
Harsh

Distribution of Days - Harsh.pbix (125.7 KB)

1 Like
7

Thankyou that’s brilliant! I knew I had to create to create a virtual table of dates but couldn’t work out how.