Dax Calculated Column Based on (not linked) Slicer Max Date Value

[steadydriver]

Hi

I'm struggling to create a Dax Calculated Column Based on a (not linked) Slicer Max Date Value

This is because I've got a Table of "ALLStock" records  in Power BI and from this "AllStock" Table I've copied this twice in Power Query  with copies called "SoldStock"  and "CurrentStock" based  on filters with  Sold Date being Populated or Not.

I've then created 2 Tables in Power BI, where I have a Date Table Slicer

The Date Table is linked on 

i)To the "SoldStock" on SoldDate

ii)To the "CurrentStock" on Bought Date

This works fine and when the user changes the Date Slicer To and From values ,  the Dashboard Changes and the tables for "Sold Stock" and "Current Stock" adjust.

However the issue is on "AllStock" table or some other way, I want to add a caclulated column called "Stock Status" on Slicer Date  which will always include ALL records in the table, but  recalcualte the "Stock Status" column  when the slicer dates change, as below

Stock Status = IF((ISBLANK(AllStock[Sold Date])&& AllStock[Bought Date]<=[Max Slicer Date])||(AllStock[Sold Date]>[Max Slicer Date] && AllStock[Bought Date]<=[Max Slicer Date]) ,"In Stock at Slicer Date","Not in Stock At Slicer Date")

I've put the [Max Slicer Date] in a Measure which is simply based on the date table.
Max Slicer Date = LASTDATE('Date'[Date])

The Problem is, If I link the "ALLStock" table (on either Bought or Sold Date) to the Date Slicer Table it will filter based on the Slicer Max Date, If I don't link it then I can't get the Calculated Column to Recognise the  [Max Slicer Date].

How can I do this?

Thanks

Item	Bought Date	Sold Date
Old  i	01/04/2018 00:00	01/11/2022 00:00
Old  ii	06/08/2019 00:00	20/02/2022 00:00
Old  iii	11/08/2019 00:00
A	01/01/2022 00:00
B	01/01/2022 00:00	30/09/2022 00:00
C	01/02/2022 00:00	20/02/2022 00:00

[steadydriver]

Hi Thanks for getting back to me.

What I want to do is

1) Create a dynaically calculated either measure or column (what ever works!) which will be based on the Date Slicer Max Value, therefore

If Item A, Sold Date = 1st Nov 22, the new calcualted measure or column would 

-Return "In Stock" (when Max Slicer is set to 31st Oct 22 or Earlier)

-Return "Sold" (when the Max Slicer is set to 1st Nov 22 or Laster). 

2)I then want to display on a visual table ALL items (not filtered by the date slicer)  in my "All Stock" table with the above measure / calculated column added.

Is there any way to do this, as the problem I seem to have is 

-If you link the date slicer (from the master date table) to the "ALL Stock" Table, it will filter the All Stock Table based on what you put in the Slicer (where I want the visual to return all records without a slicer)

- or if you remove the link between the date table in the slicer and the "All Stock" table you can't get any measures or calculated columns to work as you need DAX to somehow pass in the MAX Slicer Data Value.

I hope this clarifies the situation.

Thanks

[v-yueyunzh-msft · Accepted solution]

Hi , @steadydriver 

Thank you for your quick response.According to your description, you want to get the ""In Stock at Slicer Date","Not in Stock At Slicer Date"" by your logic.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We need to click "New Table" and enter as a slicer:

We do not need to create a relationship between two tables.

Date = CALENDAR( FIRSTDATE('AllStock'[Bought Date]) ,LASTDATE('AllStock'[Sold Date]))

(3)Then we need to create a measure:

Measure = var _max_Slicer= MAX('Date'[Date])
var _current_sold_date = MAX('AllStock'[Sold Date])
var _current_bought_date=MAX('AllStock'[Bought Date])
return
 IF((ISBLANK(_current_sold_date)&& _current_bought_date<=_max_Slicer)||(_current_sold_date>_max_Slicer && _current_bought_date<=_max_Slicer) ,"In Stock at Slicer Date","Not in Stock At Slicer Date")

(4)Then we can put the field we need on the visual and we will meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[steadydriver]

Hi, This Solution Works Perfectly.  Thanks Very Much

[v-yueyunzh-msft]

Hi , @steadydriver 

Based on your description, you want to get the selection of slicers in the newly created column? There is no filter context in the new column, there is no way to get the selection in your slicer, if you want to use the slicer to judge, it is recommended to use the measure method to achieve your needs.

For your question, I don't quite understand your needs, you can try to provide us with detailed data and the fields and visuals you want to place, and the results you want to output in the end.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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