Forum Discussion
Post PatronLast transaction date - Price
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
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.
6 Replies
- amitchandak
Super User
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_AirThanks 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]))
- Ritesh_Air
Anonymous Yuna, yes, that's what I am looking for. Can you please let me know how did you reach that?
TiA
Ritesh
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.
