Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
153 | |
101 | |
82 | |
63 | |
52 |