Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi,
have a dataset with items, confirmed date, stock prepared amount in euro and confirmed end of month.
What i want to do is checking whether the confirmed date is before the confirmed end of month date and if this is the case then i want to see the stock prepared amount in euros.
Example
item confirmed date (datetime format) stock prepared amount confirmed End of month (datetime)
9999 08-10-2019 00:00:00 1815,55 01-11-2019 00:00:00
8888 17-09-2019 00:00:00 2322,67 01-08-2019 00:00:00
So, in the first example i should have a column with the value 1815,55 and in the second example it should be 0
any ideas
grz
M
Solved! Go to Solution.
HI @emveha ,
My formula is calculated column, you can't use earlier function in measure. You can try to use following measure formula if it works:
Valid stock prepared amount = VAR currItem = MAX ( Table[Item] ) RETURN CALCULATE ( SUM ( Table[stock prepared amount] ), FILTER ( ALL ( Table ), [Item] = currItem && [confirmed date] < [confirmed End of month] ) )
Regards,
Xiaoxin Sheng
Hi @emveha ,
You can test with following calculated column formula if it suitable for your requirement:
Valid stock prepared amount = CALCULATE ( SUM ( Table[stock prepared amount] ), FILTER ( ALL ( Table ), [Item] = EARLIER ( Table[Item] ) && [confirmed date] < [confirmed End of month] ) )
Regards,
Xiaoxin Sheng
hi Xiaoxin,
thank you for your swift response, tried to make it work but as soon as i want to use the part "EARLIER" then i get errors. According to PBI, the system expects this; "EARLIER(Columnname[Number])returns the value in the column prio to the specified number of table scans (default is1)". My measure looks like:
potential stock prepared amount=
HI @emveha ,
My formula is calculated column, you can't use earlier function in measure. You can try to use following measure formula if it works:
Valid stock prepared amount = VAR currItem = MAX ( Table[Item] ) RETURN CALCULATE ( SUM ( Table[stock prepared amount] ), FILTER ( ALL ( Table ), [Item] = currItem && [confirmed date] < [confirmed End of month] ) )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |