cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
3 REPLIES 3
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

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

Anonymous
Not applicable

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors