cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
emveha Member
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
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
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: | |
emveha Member
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
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: | |

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 56 members 997 guests
Please welcome our newest community members: