Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks,
My aim is to calculate a column that gives the difference of days between max date selected in the date slicer and the dates present in transaction table.
I have 2 tables - DateMaster and TransactionTable. The date columns are DateID and PostingDate respectively which are related with one to many relationship.
Eg: If "To date" in the date slicer is 15-03-2019, then the calculated column should return difference between 15-03-2019 and 31-03-2015 (refer the sample data sheet) in the first row and so on.
Please help with some formulae.
Already tried the following -
This gives all the values as 0 as MaxDate at row level will become the same as PostingDate.
Thanks in advance,
Suguna.
Hi @bidevsugmen ,
You can create measure DaysDynamic, and then delate the relationship between DateMaster and TransactionTable.
DaysDynamic = DATEDIFF([MaxDate],SELECTEDVALUE(TransactionTable[PostingDate]),DAY)
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeFsxv8QyiBJhQhQJy...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai
Thanks for your reply.
The slicer that I am using in my case is date range slicer. In that case, I believe SELECTEDVALUE will not work as it will return more than one value.
Kind regards,
Suguna.
Hi @bidevsugmen
I've used the measure below to get the date difference:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |