Dax measure: value from calculated column if condition is met, otherwise value 100

[Anonymous]

Hi!

I am wondering if it is possible to create the Desired value with a dax measure. I have a fact table with a Date and Transaction column. I have created a calcuated column and there is also a Date slicer. In the example below, the slicer value is on or after 1-1-2019. 

I am looking for a solution where I get a value of 100 if there hasn't been any buy transaction in the selected period. For example:

• Between 1-1-2019 and 5-1-2019, there hasn't been a buy transaction registred. For those dates I want a value of 100
• At 6-1-2019, there is a buy transaction. For this date and all following dates, I want the value from the calculated column.
• If the slicer value is on or after 7-1-2019, the desired value is 100 for the dates from 7-1-2019 till the first buy transaction (somewhere in the future).

Date	Transaction	Calc. Column	Desired value
1-1-2019		112	100
2-1-2019		143	100
3-1-2019	sell	100	100
4-1-2019		100	100
5-1-2019		100	100
6-1-2019	buy	100	100
7-1-2019		144	144
8-1-2019		127	127
9-1-2019		115	115
10-1-2019		110	110

Thank you for any help you can offer!

[kentyler]

How would the desired outcomes look if there was more than one seel/buy pair ? Can there be more than one sell/buy pair ?

[Anonymous]

Hello @kentyler ,

Thanks for your responses, I hope this will clarify:

There are many buys and many sells. However a buy is always followed by a sell. And a sell is always followed by a buy. Below you will find the general structure of the table.

At the buy of sell date, the result of the calculated column has always a value of 100.

Between a sell and (the next) buy date, the result of the calculated column has always a value of 100.
Between a buy and (the next) sell date, the result of the calculated column varies.

Normally, the first transaction is always a buy transaction. However, when the Date slicer is used, it is possible that the first transaction is a sell. In this case, I am looking for a value of 100 instead of the result of the calculated column.

Date	Transaction	Calc. Column	Desired value
24-9-2018		100	100
25-9-2018		100	100
26-9-2018	buy	100	100
27-9-2018		100	100
28-9-2018		96	96
1-10-2018		96	96
26-10-2018		90	90
29-10-2018	sell	100	100
30-10-2018		100	100
1-2-2019		100	100
4-2-2019		100	100
5-2-2019		100	100
6-2-2019	buy	100	100
7-2-2019		98	98
8-2-2019		97	97
16-5-2019		103	103
17-5-2019		101	101
20-5-2019	sell	100	100
21-5-2019		100	100
24-7-2019	buy	100	100
25-7-2019		101	101
5-8-2019		92	92
6-8-2019		91	91
7-8-2019	sell	100	100
8-8-2019		100	100