Calculate availability in a date intervalle

[Ade1991]

Hello,

I make stats for a company that manages billboards.

I have a list of billboards and for each billboard, its date of assembly and disassembly.

I would like to be able to know my faces mounted and existing on December 31st of each year (or another date). This means that at a date X, the billboard is considered to be mounted if X is later than the date of assembly and earlier than the date of disassembly.

I would also like to be able to track the evolution of my number of panels over time.

I'm not sure how to proceed? Filters? Measure? Time Intelligence function ?

And in your opinion, what would be the best viz?

[v-alq-msft · Accepted solution]

Hi, @Ade1991 

Based on your description, I create data to reproduce your scenario.

Firstly, you can create a date range and a table as follows.

Dates = CALENDAR(MIN(Table[Date]),MAX(Table[Date])) 

Secondly, you may create a measure like below.

TotalNumber = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[assembly date]<=MIN('Dates'[Date]) && 'Table'[disassembly date] >= MAX('Dates'[Date

Result:

If I misunderstand the your thoughts, please show me the sample data and  expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ade1991]

Thanks, that's exactly my situation.

In your formula, how can i add a condition for this formlua

TotalNumber = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[assembly date]<=MIN('Dates'[Date]) && 'Table'[disassembly date] >= MAX('Dates'[Date]

where >= MAX (date) or is blank ?

Thanks

[kentyler]

I would say the first question is what is called "granularity". When you say "over time" do you want to track your numbers by month, by week, by day ?

A table that had one record for each day each billboard was up would greatly simplify a lot of calculations.
Such a table could be generated from your table listing the start and end date for each billboard in EXCEL or SQL.

A bar chart or a line graph would probably work well to track a number that changes over time. If you want to compare multiple years a line graph might be better, because you could put 2 or 3 lines on it to represent different years.
The calculations should probably be done in measures, except for calculations like the number of days or months the board is up...that would be a good candidate for a calculated column, or for being calculated in the system you have the data in, before you load the data into power bi.
This would be a good conversation for a screen share... as there are a lot of different factors that could come into play. I'm around all day tomorrow, if you're interested. Send me your email and give me a time and I'll email you a zoom invitation 2-3 PST is already taken.

I'm a personal Power BI Trainer . I learn something every time I answer a question