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 need to track the # of times a column has a set value for by [Product Name] and [Date].
The column that needs to be tracked is the [BuyBoxStatus] column.
Once the value in [BuyBoxStatus] changes, the count would restart back to 1 again.
The intended result is the last column [Days in Current Status]
Example:
-ProductName- | -BuyBoxStatus- | -Date- | -Days in Current Status- |
A | 2 | 12/7/2020 | 1 |
B | 1 | 12/7/2020 | 1 |
A | 2 | 12/8/2020 | 2 |
B | 1 | 12/8/2020 | 2 |
A | 2 | 12/9/2020 | 3 |
B | 1 | 12/9/2020 | 3 |
A | 5 | 12/10/2020 | 1 |
B | 3 | 12/10/2020 | 1 |
A | 5 | 12/11/2020 | 2 |
How can i achieve this either in a measure or Calculated Column?
Solved! Go to Solution.
I'm not surprised it is slow. There is a lot of calculation going on, and I didn't know how many rows you have in your data. Here is the measure version of it. It works in a table visual with your product name column, and with or without the Date column from that table. Of course, it will be much faster with just the product name (it will show the days it has been in the current status).
Days Current Status =
VAR vCurrentStatus =
LASTNONBLANKVALUE (
'Status'[-Date-],
MAX ( 'Status'[-BuyBoxStatus-] )
)
VAR vLatestDate =
MAX ( 'Status'[-Date-] )
VAR vMinDateSameStatus =
CALCULATE (
MIN ( 'Status'[-Date-] ),
FILTER (
ALL ( 'Status'[-Date-] ),
'Status'[-Date-] <= vLatestDate
),
'Status'[-BuyBoxStatus-] = vCurrentStatus
)
VAR vPrevDate =
CALCULATE (
MAX ( 'Status'[-Date-] ),
FILTER (
ALL ( 'Status'[-Date-] ),
'Status'[-Date-] < vLatestDate
),
'Status'[-BuyBoxStatus-] <> vCurrentStatus
)
VAR vResult =
IF (
vPrevDate > vMinDateSameStatus,
DATEDIFF (
vPrevDate,
vLatestDate,
DAY
),
DATEDIFF (
vMinDateSameStatus,
vLatestDate,
DAY
) + 1
)
RETURN
vResult
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I'm not surprised it is slow. There is a lot of calculation going on, and I didn't know how many rows you have in your data. Here is the measure version of it. It works in a table visual with your product name column, and with or without the Date column from that table. Of course, it will be much faster with just the product name (it will show the days it has been in the current status).
Days Current Status =
VAR vCurrentStatus =
LASTNONBLANKVALUE (
'Status'[-Date-],
MAX ( 'Status'[-BuyBoxStatus-] )
)
VAR vLatestDate =
MAX ( 'Status'[-Date-] )
VAR vMinDateSameStatus =
CALCULATE (
MIN ( 'Status'[-Date-] ),
FILTER (
ALL ( 'Status'[-Date-] ),
'Status'[-Date-] <= vLatestDate
),
'Status'[-BuyBoxStatus-] = vCurrentStatus
)
VAR vPrevDate =
CALCULATE (
MAX ( 'Status'[-Date-] ),
FILTER (
ALL ( 'Status'[-Date-] ),
'Status'[-Date-] < vLatestDate
),
'Status'[-BuyBoxStatus-] <> vCurrentStatus
)
VAR vResult =
IF (
vPrevDate > vMinDateSameStatus,
DATEDIFF (
vPrevDate,
vLatestDate,
DAY
),
DATEDIFF (
vMinDateSameStatus,
vLatestDate,
DAY
) + 1
)
RETURN
vResult
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This column expression should work even if a product repeats a status. Replace table/column names with your actual ones.
Days This Status =
VAR thisdate = 'Status'[-Date-]
VAR thisstatus = 'Status'[-BuyBoxStatus-]
VAR mindatesamestatus =
CALCULATE (
MIN ( 'Status'[-Date-] ),
ALLEXCEPT (
'Status',
'Status'[-ProductName-]
),
'Status'[-BuyBoxStatus-] = thisstatus,
'Status'[-Date-] <= thisdate
)
VAR maxdatediffstatus =
CALCULATE (
MAX ( 'Status'[-Date-] ),
ALLEXCEPT (
'Status',
'Status'[-ProductName-]
),
'Status'[-BuyBoxStatus-] <> thisstatus,
'Status'[-Date-] < thisdate
)
VAR finaldate =
IF (
maxdatediffstatus > mindatesamestatus,
maxdatediffstatus,
mindatesamestatus
)
RETURN
DATEDIFF (
finaldate,
thisdate,
DAY
) + 1
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat , thanks for the solution!!
Unfortunetly it is taking quite a long time to process.
You think if I tried, I could replicate this formula in a measure using virtual tables?
this measure would simply drop in a table with productId.
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |