The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I'm new to power BI and I would really appreciate your help.
I have the following sample dataset (this sample only relates to 1 item):
ID | Status | year-month | Item_Num | Days_In_Status |
202212||11223344 | Offered for sale | 202212 | 11223344 | 212 |
202211||11223344 | Offered for sale | 202211 | 11223344 | 182 |
202210||11223344 | Offered for sale | 202210 | 11223344 | 152 |
202209||11223344 | Offered for sale | 202209 | 11223344 | 121 |
202208||11223344 | Offered for sale | 202208 | 11223344 | 91 |
202207||11223344 | Offered for sale | 202207 | 11223344 | 60 |
202301||11223344 | Sold | 202301 | 11223344 | 0 |
202206||11223344 | Offered for sale | 202206 | 11223344 | 29 |
202102||11223344 | waiting | 202102 | 11223344 | 11 |
202202||11223344 | waiting | 202202 | 11223344 | 11 |
202101||11223344 | Rented | 202101 | 11223344 | 32 |
202205||11223344 | Rented | 202205 | 11223344 | 92 |
202204||11223344 | Rented | 202204 | 11223344 | 60 |
202203||11223344 | Rented | 202203 | 11223344 | 31 |
202201||11223344 | Rented | 202201 | 11223344 | 197 |
202112||11223344 | Rented | 202112 | 11223344 | 166 |
202111||11223344 | Rented | 202111 | 11223344 | 135 |
202110||11223344 | Rented | 202110 | 11223344 | 105 |
202109||11223344 | Rented | 202109 | 11223344 | 74 |
202108||11223344 | Rented | 202108 | 11223344 | 44 |
202107||11223344 | Rented | 202107 | 11223344 | 12 |
202106||11223344 | Rented | 202106 | 11223344 | 107 |
202105||11223344 | Rented | 202105 | 11223344 | 77 |
202104||11223344 | Rented | 202104 | 11223344 | 46 |
202103||11223344 | Rented | 202103 | 11223344 | 16 |
For my analysis, I would like:
1. To select, for each item and status, the max value of 'days_In_Status' and date.
2. To sum the time difference between 'Offered to sale' and 'Sold'
3. To flag, in an added column, the items that were rented and then sold.
Can someone please guide me to the right solution?
Thank you.
Solved! Go to Solution.
Hi @OP_1234
You can refer to the following example.
1. To select, for each item and status, the max value of 'days_In_Status' and date.
You can create two measures.
Max_value_day_in_date = CALCULATE(MAX([year-month]),FILTER(ALL('Table'),[Item_Num]=MAX([Item_Num])&&[Status]=MAX([Status])))
Max_value_day_in_status = CALCULATE(MAX([Days_In_Status]),FILTER(ALL('Table'),[Item_Num]=MAX([Item_Num])&&[Status]=MAX([Status])))
2. To sum the time difference between 'Offered to sale' and 'Sold'
You can create a measure
time difference_month = var _offeredforsale=CALCULATE(MIN([year-month]),'Table'[Item_Num]=MAX([Item_Num]),'Table'[Status]="Offered for sale")
var _sold=CALCULATE(MIN([year-month]),'Table'[Item_Num]=MAX([Item_Num]),'Table'[Status]="Sold")
return DATEDIFF(DATE(LEFT(_offeredforsale,4),RIGHT(_offeredforsale,2),1),DATE(LEFT(_sold,4),RIGHT(_sold,2),1),MONTH)
3. To flag, in an added column, the items that were rented and then sold.
You can creata a calculated column
Flag = var a=FILTER('Table',[Item_Num]=EARLIER([Item_Num]))
var b=COUNTROWS(FILTER(a,[Status]="Offered for sale"))
var c=COUNTROWS(FILTER(a,[Status]="Sold"))
return IF(b>0&&c>0,1,0)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @OP_1234
You can refer to the following example.
1. To select, for each item and status, the max value of 'days_In_Status' and date.
You can create two measures.
Max_value_day_in_date = CALCULATE(MAX([year-month]),FILTER(ALL('Table'),[Item_Num]=MAX([Item_Num])&&[Status]=MAX([Status])))
Max_value_day_in_status = CALCULATE(MAX([Days_In_Status]),FILTER(ALL('Table'),[Item_Num]=MAX([Item_Num])&&[Status]=MAX([Status])))
2. To sum the time difference between 'Offered to sale' and 'Sold'
You can create a measure
time difference_month = var _offeredforsale=CALCULATE(MIN([year-month]),'Table'[Item_Num]=MAX([Item_Num]),'Table'[Status]="Offered for sale")
var _sold=CALCULATE(MIN([year-month]),'Table'[Item_Num]=MAX([Item_Num]),'Table'[Status]="Sold")
return DATEDIFF(DATE(LEFT(_offeredforsale,4),RIGHT(_offeredforsale,2),1),DATE(LEFT(_sold,4),RIGHT(_sold,2),1),MONTH)
3. To flag, in an added column, the items that were rented and then sold.
You can creata a calculated column
Flag = var a=FILTER('Table',[Item_Num]=EARLIER([Item_Num]))
var b=COUNTROWS(FILTER(a,[Status]="Offered for sale"))
var c=COUNTROWS(FILTER(a,[Status]="Sold"))
return IF(b>0&&c>0,1,0)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.