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.
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
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |