Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am connected to a tabular cube, so measures only please!
I have time series data (a revenue value) at monthly grain, and I have specific dates that are flagged for an event occurring. Some of these events are one time, while others are ongoing for a period (I have the business knowledge to differentiate the two). Any of the events (either one time or ongoing) can have multiple periods where they occur over the time series.
So, imagine that my table looks like this:
Date | Event 1 Flag | Event 2 Flag | Event 3 Flag
1/1/2015 | 0 | 0 | 0
2/1/2015 | 1 | 0 | 0
3/1/2015 | 0 | 1 | 0
4/1/2015 | 0 | 1 | 0
5/1/2015 | 0 | 0 | 1
My goal is to have a measure that calculates the duration of the ongoing events (in this example, Event #2 is ongoing). I cannot just sum the column though becuase there could be another period in the future where Event 2 is flagged. So, I need to sum only the continuous range of Event 2 Flag = 1
Solved! Go to Solution.
Hi @dkay84_PowerBI,
You can refer to below step to get sum of continuous date range total.
Steps:
1. Create a table with tag column mark the continued date range.
NewTable = VAR current_date = SELECTEDVALUE ( 'Table'[Date] ) VAR previous = current_date - 1 VAR next = current_date + 1 VAR filtedTable = FILTER ( ALL ( 'Table' ), [Event 2] = 1 ) VAR dateRange = SELECTCOLUMNS ( filtedTable, "Date", [Date] ) var Tagged=ADDCOLUMNS ( filtedTable, "Tag", IF ( OR ( AND ( [Date] IN dateRange, [Date] + 1 IN dateRange ), AND ( [Date] IN dateRange, [Date] - 1 IN dateRange ) ), 1, 0 ) ) RETURN UNION(ADDCOLUMNS(FILTER('Table',[Event 2]=0),"Tag",0),Tagged)
2. Write a formula to summary continued date range.
Running Total = VAR range_min = MAXX ( FILTER ( ALL ( NewTable ), [Date] < MAX ( [Date] ) && [Tag] = 0 ), [Date] ) VAR range_max = MINX ( FILTER ( ALL ( NewTable ), [Date] > MAX ( [Date] ) && [Tag] = 0 ), [Date] ) RETURN IF ( MAX ( [Tag] ) <> 0, IF ( range_min <> BLANK (), DATEDIFF ( range_min, range_max, DAY ) - 1, DATEDIFF ( FIRSTDATE ( ALLSELECTED ( NewTable[Date] ) ), range_max, DAY ) ) )
Result:
Regards,
Xiaoxin Sheng
Hi @dkay84_PowerBI,
You can refer to below step to get sum of continuous date range total.
Steps:
1. Create a table with tag column mark the continued date range.
NewTable = VAR current_date = SELECTEDVALUE ( 'Table'[Date] ) VAR previous = current_date - 1 VAR next = current_date + 1 VAR filtedTable = FILTER ( ALL ( 'Table' ), [Event 2] = 1 ) VAR dateRange = SELECTCOLUMNS ( filtedTable, "Date", [Date] ) var Tagged=ADDCOLUMNS ( filtedTable, "Tag", IF ( OR ( AND ( [Date] IN dateRange, [Date] + 1 IN dateRange ), AND ( [Date] IN dateRange, [Date] - 1 IN dateRange ) ), 1, 0 ) ) RETURN UNION(ADDCOLUMNS(FILTER('Table',[Event 2]=0),"Tag",0),Tagged)
2. Write a formula to summary continued date range.
Running Total = VAR range_min = MAXX ( FILTER ( ALL ( NewTable ), [Date] < MAX ( [Date] ) && [Tag] = 0 ), [Date] ) VAR range_max = MINX ( FILTER ( ALL ( NewTable ), [Date] > MAX ( [Date] ) && [Tag] = 0 ), [Date] ) RETURN IF ( MAX ( [Tag] ) <> 0, IF ( range_min <> BLANK (), DATEDIFF ( range_min, range_max, DAY ) - 1, DATEDIFF ( FIRSTDATE ( ALLSELECTED ( NewTable[Date] ) ), range_max, DAY ) ) )
Result:
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |