Down-days per month without explicit rows for every month

FMPE — Wed, 09 Aug 2023 11:17:38 GMT

Hi community!

I'm going to get straight to the point. The issues I'm having is that I have a table that shows down-days for different types of equipment/machinery, meaning days on which the machine was out of order for one reason or another. The table is set up in such a way that it contains the following:

ID of the machine (a unique identifier).
Type of machine.
Date on which the machine went out of order.
Date on which the machine was reinstated.
Off-days in the initial error month
Off-days in the month it was reinstated
Off-days in months between the start- and end month (if applicable)

What I want to do is create a visualization that shows the total number of down days for a given period, on which you're able to slice by both month/year, machine and/or machine type. The only way I can currently think of doing this is creating a table that contains an try for every machine every month and how many down days they have in the given month. As far as I can tell, this should be possible, but it seems to be to be a very messy solution, and I feel like it should be doable with a measure/measures.

Sorry if the issues / description is unclear, and please don't hesitate to ask for clarification or additional information. Unfortunately, I am not able to share a .pbix file, as the data contained is confidential, so I'll have to describe it as best I can.

Thanks in advance!

Re: Down-days per month without explicit rows for every month

FMPE — Wed, 09 Aug 2023 12:18:38 GMT

Okay, I think I partly understand the issues I'm having now, and potentially how I might solve it, although I am still not sure how to get there. I think the issues I'm having stems from the fact that my calendar table, by which I slice, is connected via relation to the error start date, i.e. the day on which the error started.

The reason that that relation is causing issues is in the cases where an error period covers multiple months, e.g:

ID	TYPE	ERROR FROM	ERROR TO
TR420	Trailer	20/01/2023	03/03/2023

When I'm filtering by month to see the error days per month, I'll only be shown the number of error days from month in which the period started, as the remaining days are "sliced off".

I'm thinking of solving this by expanding the table so an error period like the one previously mentioned would we split into three seperate rows like so:

ID	TYPE	ERROR FROM	ERROR TO
TR420	Trailer	20/01/2023	31/01/2023
TR420	Trailer	01/02/2023	28/02/2023
TR420	Trailer	01/03/2023	03/03/2023

However, I'm still not where whether this is the best solution, or indeed feasible to do so, so please do let me know whether this approach is appropriate and, if so, how I might go about implementing it.

topic Down-days per month without explicit rows for every month in DAX Commands and Tips

Down-days per month without explicit rows for every month

Re: Down-days per month without explicit rows for every month