Rolling calculation with conditions

[Anonymous]

Hi all, 

i would like to make this rolling calculation for incident free working hours. This should be 

=Sum(Workinghours) * (Days wiithout incidents in a month/Days in month)

as seen in the table below (red is the column i would like to generate)

The dates are generated through a calendar table, and the incident dates and working hours are also separates  tables which i have linked in the model. 

how is it possible to make this rolling calculation in power bi ?

Thank you ! 

Miguel

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

If not please list the mathematical formula for this column.

Best regards,
Lionel Chen

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

[Anonymous]

Hello Again,

I would like to calculate the days between the last incindent and end of the month (the date difference) and write it in the working hours table. following challenges:

- How can the formula know which one was the last date in the month 

- 1 table has months the other table has specific dates, how can look up this values and write them down in working hours table under " days between last incindent and end of month "

I think we need to do it step by step. 

i wil ltry to describe the problem with an example file 

i have 3 tables: 

1 Working hours: with sites and organisations and working hour numbers, month and year of the working hours

2  Incident : with incindent dates and also site and organisations

3 Calendar table connecting the both

in order to make the calendar table compatible with the working hours table i had to make a "Dummy" date table with always the first of the month (SOM).

as you can see both tables have common company site and organisation.

for example the Result for January 2020 for organisation alpha, site a, company 1 in January should be "7" which is 31.01 - 24.01.2020. 

I hope this explains the situation better

Miguel

[Anonymous]

sorry not yet i willl post as soon as i have a solution or describe better the problem,

Best regards,

Miguel

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

example of rolling with a date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))

Rolling 3 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))

Rolling 365 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-365,Day))
Rolling 1 year = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-12,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Thank you i will try it and let you know !

Best regards,

Miguel