Last transaction date - Price

[Ritesh_Air]

Hi Experts,

How do I ignore date slicer which have dates in future to show last transaction date of an item in the table?

Here is my problem:  

I want to find out the last transaction date and the price of an item.

Problem is, I have a slicer which has dates in future so I am getting blanks for item's sold date and it's price in the table.

I want to see something like this: Included in the example tab of attached pbix.

I have included the pbix and looking for what is shown in "Example" tab. How do I achieve that in "Net Sales Forecast" tab?

https://drive.google.com/file/d/1FpwrV-F2s_hRIvIMkrtQC35ncitgpV7R/view?usp=sharing

TIA,
-RK

[v-yuaj-msft]

Hi @Ritesh_Air ,

I did a test. Is the following result what you want?

Best Regards,

Yuna

[Ritesh_Air]

@v-yuaj-msft Yuna, yes, that's what I am looking for. Can you please let me know how did you reach that?

TiA

Ritesh

[v-yuaj-msft · Accepted solution]

Hi @Ritesh_Air ,

Thanks for your reply.

The following is my own solution. I would be honored if others have a better idea and can share it with me.

Based on your description, you can create a calculated table as follows.

Table = 

var x1=SUMMARIZE (

 FILTER (

 ALL ( 'Sales Order' ),

 RELATED ( 'Item'[Item Number ID] ) <> BLANK ()

 && RELATED ( Customer[Has Sales] ) = 1

 ),

 'Date'[Week Start],

 'Customer'[Parent Retailer AB Number],

 'Item'[Item Number ID],

 "date day",MAX('Sales Order'[Day Date]),

 "max_date",[Max - date],

 "maxprice",[Max - date Price]

)

return

ADDCOLUMNS(x1,"max_price",MAXX(FILTER(x1,[Item Number ID]=EARLIER('Item'[Item Number ID])),[maxprice])

)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ritesh_Air , Try meausre like

calculate(lastnonblankvalue(Table[Date], max(Table[Price])), allexcept(Table, Table[item]))

or

calculate(lastnonblankvalue(Table[Date], max(Table[Price])), filter(all(Table), Table[item] =max(Table[item])))

[Ritesh_Air]

Thanks Amit, I have tried both the formulas but I get blank, when I try to put it in the table shown in "Net Sales Forecast" tab in the attached pbix.

[amitchandak]

@Ritesh_Air , Try like

Max - date Price = CALCULATE(LASTNONBLANKVALUE('Date'[Week Start], [Net Sales LY]),ALLEXCEPT('Item','Item'[Item Number ID]),ALLEXCEPT(Customer,Customer[Parent Retailer AB Number]),ALLSELECTED('Date'[Week Start]))