Set column values based on selection from different table

[CAG]

Hello everyone,

I am already quite far (I hope) with the solution, I only need a small extra piece. I hope anyone can help further, many thanks already ahead!

In a project I am working for we would like to see historical Data. The main question raised is: How did the situation look like 2 years ago? Since it is a very dynamic dataset where the master data changes a lot, we do this by creating snapshots of the data every day.

In order to reduce the data volume, we have introduced Valid from and Valid to dates. That means, that not every day the whole data is stored with a new snapshot date, only the rows which have changed get a new entry in the database, the valid from date of the new row is set to 'today' and 'valid to' goes until year 9999, the old entry gets the 'valid to' -date of yesterday.

When the user now wants to see historic data, he should pick only one date. The data should then be filtered so that the selected date is between the valid from and valid to date. E.g:

• Selected date: 20.07.2017
• All rows where the date is between valid from and valid to date should show up (e.g. 1.1.1900 until 31.12.9999 or 20.07.2017 until 31.12.2017 etc.)

In order for the user to pick a date, I have loaded a separate table with dates. I have also added a column to the dates table and to the other tables with 'valid from' / 'valid to' -dates which converts the date into a number.

I would like to do the following:

• Insert a date picker from which the user is able to pick his date
• Create a calculated column in the tables with the valid from / valid to which says:

If (selected date Value >= Valid from Value) AND (selected Date Value <= Valid to Value) write TRUE else FALSE

• I can then apply a filter on the whole table which filters on Calculated Column = TRUE

I am hitting the following issues:

• There is no date picker which enables me to pick just one date. My solution is to pick the Date slicer and select “after” in the menu, then I cut off the right half of the slicer. It then looks like this:
  initial: now shows as:
  Power BI now gives me a range of dates and date values. To solve it, I use the MIN() Formula, so I only get one date, the date which the user is able to select. (Formula: Selected Date = MIN(Date Value))
• So far so good, the only issue is that now my calculated column does not allow me to use the 'Selected Date' Measure, because it says that I can’t use a MIN function for a calculated column. However, I need a measure to be able to select the “Selected date” from the Date table.

Now finally the question: Do you know which formula I could use instead of MIN, so I can use the Selected Date Measure for my calculated column?

If you have any other ideas on how to solve it that would also be appreciated!

Update: Also in the import mode it doesnt seem to work, since calculated columns are only calculated when loading / refreshing the data. Does anyone have any idea on how to solve it?

Thanks very much ahead!

CAG

[vanessafvg]

@CAG that was a lot of writing and i am visual person, so i might have missed some important bits, but have you tried, firstdate?

https://msdn.microsoft.com/en-us/library/ee634806.aspx

[CAG]

Hi Vanessa,

sorry for the amount of text and thanks very much for your response.

Firstdate doesnt work either, I am connected via Directquery, thus the formula is not allowed.

also I am not sure if this would solve my problem, since I would need to look at the value, not at the Date.