Automated table after value check

[Anonymous]

Hi community, this is a though one:

I have this table composed by the departure day, some markets and the values which are a daily average of some figures.

The departure date range starts spans over 80 days (6 months) with variable start days (generally it takes from the day of update +60 (initial day) + 180 (final date of report).

As we need to update a platform we have to make sure that dayily average is above 4 so the model can create a table where that looks like this:

where the 2 yellow fields are a range established after checking that the average figure is more than 4. So for example if for a day or more that figure is below 4, it should create a break and add a line. The other fields are populated from other queries. 

I could do somehting like this on excel but was a bit of an headache, hope we can work it out on PBI.

Thank you for your great support

[v-yulgu-msft]

Hi @Anonymous,

You want to generate the below table based on the above one, right? To help us better understand your requirement, please make the logic of calculation more clear.

Based on my assumption, the first data table is dynamic. The highlighted data range is the expected result, which means in this period, the daily average is always over 4. In other words, each day has more than 4. But, if on one day, the average is below 4, the continual dates is broken. Then, it should be recalculate on next start day where the average is over 4 again.

If I get your requirement, please show us a more detailed image of your desired output, based on above sample data.

Regards,

Yuliana Gu

[Anonymous]

Hi Yuliana, 

yes basically your assumption is correct. 

the table that you reported in your post is the result. 

In my mind the way this has to work is a bit long (and klunky)  as I should establish 180 if statements to make sure if the value is >4 and with another statement makes the date range.

And even more complicated in makes as many lines according to the number of date ranges found. 

In excel a nice macro would make this happen. don't know here >X