Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emveha
Resolver III
Resolver III

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.