March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
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:
If (selected date Value >= Valid from Value) AND (selected Date Value <= Valid to Value) write TRUE else FALSE
I am hitting the following issues:
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
@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
Proud to be a Super User!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |