Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a table as below, This table only updated if something changed (such as total or status).
Item# | Status | Open date | Closed date | Updated date | Total |
A123 | O | 12/1/2020 | 12/1/2020 | $100 | |
A123 | C | 12/1/2020 | 2/1/2021 | 2/1/2021 | $50 |
A123 | RO | 12/1/2020 | 5/1/2021 | $70 | |
B456 | O | 2/1/2021 | 2/1/2021 | $60 | |
B456 | C | 2/1/2021 | 4/1/2021 | 4/1/2021 | $40 |
What we need it to be is like below. To fill out the gap between updated date and calculate the cumulative table.
Item# | Status | Open date | Closed date | Updated date | Total | |
A123 | O | 12/1/2020 | 12/1/2020 | $100 | ||
A123 | O | 12/1/2020 | 1/1/2021 | $100 | new | |
A123 | C | 12/1/2020 | 2/1/2021 | 2/1/2021 | $150 | |
A123 | C | 12/1/2020 | 2/1/2021 | 3/1/2021 | $150 | new |
A123 | C | 12/1/2020 | 2/1/2021 | 4/1/2021 | $150 | new |
A123 | RO | 12/1/2020 | 5/1/2021 | $220 | ||
B456 | O | 2/1/2021 | 2/1/2021 | $60 | ||
B456 | O | 2/1/2021 | 3/1/2021 | $60 | new | |
B456 | C | 2/1/2021 | 4/1/2021 | 4/1/2021 | $100 |
The issue is there are more than 80000 item#, and some new item# be added every month. I am new with Power BI, please help. Thanks in advance. @amitchandak
Hi @wuxxx711 ,
unfortunately, I have not solved this problem.😂
Hi @wuxxx711 ,
In my opinion,What can be done at the moment is to split the calculation first and then merge the data at the end.
At first I created a separate date table to implement the row data addition, but when
When update_date=2/1/2020, Item# has A123 and B456, and the new date table update_date=2/1/2020 returns one of them. So I think the best way to do this is to split it up and calculate it separately and then merge it at the end.
Best Regards
Lucien
Hi @wuxxx711 ,
Try the following steps:
1,create a new date table :
2.create new column on the new table :
Item# = MAX('Table'[Item#])
3. use the following measure:
Status2 =
VAR test1 =
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
'Table'[Updated date] = MAX ( Update_date[Updated date] )
)
)
VAR n = 1
VAR test2 =
IF (
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + n, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
> BLANK (),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + N, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + N + 1, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
)
VAR m=3
VAR test5 =
IF (
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
> BLANK (),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m + 1, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
)
VAR test4 =
IF ( test1 <> BLANK (), test1, if(test2<>blank(),test2,test5) )
RETURN
test4
If there are still blank values returned, you can add the loop section a few more times:
VAR m=3
VAR test5 =
IF (
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
> BLANK (),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
DATE ( YEAR ( 'Table'[Updated date] ), MONTH ( 'Table'[Updated date] ) + m + 1, DAY ( 'Table'[Updated date] ) )
= MAX ( Update_date[Updated date] )
)
)
)
Then base on status ,new column:
and final :
You could download my pbix file if you need.
Wish it is helpful for you !
Best Regards
Lucien
Thanks for your answer, it looks good. I just added a new item# B456 in my example. In this case, how can we copy a new row for B456? Thanks!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |