Problem distributing days between two dates in each month

[Philippe_Jamon]

Hello everybody,

I have a problem with distributing days between two dates in each month.

I have a model where i need to calculate loss days of each incident (i have a column DateLossStart and DaysLossEnd) and distribute it on each month (for each incident).

I tried to create a measure like that :

Number of days by month = 
CALCULATE(
    COUNTX(
        SUMMARIZE(
            FILTER(
                CROSSJOIN('Accidents de travail','Dates'),
                'Dates'[Date] >= 'Accidents de travail'[DateDebutArret]
                    && 'Dates'[Date] <= 'Accidents de travail'[DateFinArret]
            ),
'Accidents de travail'[NOM],            'Dates'[Date]
        ),
        'Dates'[Date]
    )
)

The measure works in a visualisation only if I don't link my date table to my main data table, otherwise it just give me the total of loss day for an incident only on the month of the date of the incident.

The problem is that I need to link my date table to be abble to use it for filter and for other measures (in my example I need to have another measure named "Nb AT avec arrêt")

Here's a pbix as example of my data.

Thanks for your help.

Philippe.

[parry2k]

@Philippe_Jamon glad to hear. Best!

[Philippe_Jamon]

Hello everybody,

I make a little update on that post.

Everything was working fine with the distribution of my loss days until I had and incident that has loss days from december 2023 to 15th of January 2024. My problem is that even if i filter the date for 2023, it shows 15 days in January in my visualisation.

Thanks for your help.

Br,

Philippe.

[parry2k]

@Philippe_Jamon Yes in a scenario like this you have to work with disconnected tables. You can still have your regular date table and a disconnected table can be used to achieve the result, and based on the selection, you can filter your regular date table as well.

[Philippe_Jamon]

Thanks for your help @parry2k , I managed to do what i wanted by creating a second date table only for my measure.

Have a nice day.

[parry2k]

@Philippe_Jamon maybe you can look into something similar at this video, might require some tweaks to meet your needs but it will get you going:

Revenue distribution in Power BI: Advanced DAX Techniques Revealed - YouTube

[Philippe_Jamon]

Hi @parry2k ,

Thanks for your answer.

I watched the video, and i think this won't work for me because in the video, there's no relation between the calendar table and the data table.

My problem is that i want to be abble to filter the data table with the calendar table, and so i need to set relation between the two.

But when i do that, my measure for the loss days per month won't work and it shows me only the loss days of the declaration date (and not all the days between start date and end date).

Thanks again for your help.

Philippe.