Forum Discussion
Pivot and aggregate data
Unfortunately it is not exaclty what I am looking for. I try to explain myself better: as regards the grouping of the the dates it's not ok to calculate the min date and the max date in 1 day, because inside one day I am looking for grouping all occurence of date range where dhealth is not equal to 100%. eg:
if I have
1/1/2021 8:00 - 1/1/2021 9:30 Health = 80%
1/1/2021 11:30 - 1/1/2021 12:00 health = 90%
1/1/2021 12:00 - 1/1/2021 13:00 health = 100%
1/1/2021 13:00 - 1/1(2021 13:30 health = 70%
In my desidered table I will need two grouping inside day 1/1/2021, respectively:
1/1/2021 11:30 - 1/1/2021 12:00 healt = average(80, 90)
1/1/2021 1/1/2021 13:00 - 1/1(2021 13:30 health = 70%
Let me know if you need further clarifications
Thank you
Hi PowerBI88 ,
I am a bit unclear about the calculation logic here:
Do you mean that all date ranges before 100% will be calculated "average value"? And just keep the last date range?
In addition, how to judge a date range in your orginal data structure, all datetime records in one column?
Best Regards,
Icey
Sorry My mistake, the first date range starts with hour 8:00 till 12:00.
Do you mean that all date ranges before 100% will be calculated "average value"? And just keep the last date range?--> yes, th econdition to look for is health not equal to 100%
In addition, how to judge a date range in your orginal data structure, all datetime records in one column? --> yes they are all in a column
Hi PowerBI88 ,
Sorry I didn't make it clear. For my last question, what I want to confirm is that the health value is for a date range, but in your orginal data structure, all datetime records are in one column. Then how do you define a date range? And how do you define the health value?
Best Regards,
Icey
It is not directly defined in the data. You can infer it by iterating over the table till you meet the 100% health value. You have to look for the end date before you meet the row with the 100% health value.
Let me know if it is clear.