cancel
Showing results for
Did you mean:
Highlighted
Member

## confirmed date < confirmed date end of month then take stock prepared amount in euro

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: confirmed date < confirmed date end of month then take stock prepared amount in euro

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
3 REPLIES 3
Community Support Team

## Re: confirmed date < confirmed date end of month then take stock prepared amount in euro

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: confirmed date < confirmed date end of month then take stock prepared amount in euro

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=

"CALCULATE(SUM(Orders[StockPreparedEURO]);FILTER(ALL(Orders);[Product_Code]=EARLIER(Orders[ConfirmedDate]) BUT then i get this error...the syntax for ')' is incorrect (DAX(CALCULATE.....)"
any idea how to solve it
Thanks!
M
Community Support Team

## Re: confirmed date < confirmed date end of month then take stock prepared amount in euro

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 56 members 997 guests
Recent signins: