How to calculate values based on condition to be be added in specified date range

[Anonymous]

Hi Power BI Community Support,

I am new to the power bi looking support for the below problem statement.

I have table as attached for that I wanted to created calculated column that should store values based on condition.

Condition:

e.g. in the highlighted row days_diff = 70 (difference between Start_Date and End_Date is 70 days) and country is C

so the condition is if days_diff >1 (here days_diff = 70) then 24 hours should be added for next consecutive days based on the value in days_diff (here next 70 days "hours" column should be filled with 24 hours) for country C only.

OR "calculated_column" / "hours" column rows should be filled with 24 hours between "Start_Date" and "End_Date"

As example shown in "calculated_column"

I hope the problem statement is clear and looking forward support.

thanks!

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Refer this:

Column 1 = IF('Table'[diff]>1,24)

Column 2 = 
var _lastkey = CALCULATE(MAX('Table'[key]),FILTER('Table','Table'[country]=EARLIER('Table'[country])&&'Table'[Column]=24&&'Table'[key]<EARLIER('Table'[key])))
var _lastdiff = CALCULATE(SUM('Table'[diff]),FILTER('Table','Table'[country]=EARLIER('Table'[country])&&'Table'[Column]=24&&'Table'[key]<EARLIER('Table'[key])))
return
IF('Table'[Column]=24,24,IF('Table'[key]<=_lastkey+_lastdiff,24,BLANK()))

Best Regards,

Jay

[selimovd]

Hey @Anonymous ,

your description is a little confusing.

Is that what you want:

calculated_column =
IF(
    myTable[days_diff] > 1,
    24
)

Otherwise can you please tell again in a more clear way what the condistions are?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd 

thanks for your quick response. 

the provided query will be fill 24 hours to that row only.  I wanted to add that 24hours to next days also (whatever days are in "days_diff" column).

this 24 hours should be populated to all rows between Start_Date and End_Date from that record where days_diff >1 and for that "country" only
just like I shown in "calculated_column" from the snapshot.

thanks!