Need to derive the Date field based on existing Dates

[likhithar]

Hello All,

I have 2 tables Treasury and Int Rate.

In Treasury Table I have Transactions based on Date ,but even I add few Date rows based on Int Rate Dates also.My Fin Year starts by 01-Apr and ends by 31-Mar
In the below scenario my Date selection is between 01-Apr-22 and 30-Sep-22.

Ex : Scenario
In Treasury Table my first transaction happened on 26 Jun where as First Int Reset Date in Cur Fin is 01-05-22

So my First row Mat Date should be 30-Apr-22 because First Int Reset Date in Cur Fin is 01-05-22 ,so we considering the prior Day as Mat Date and Availment Date is prior Int Reset Date i.e 01-03-2022

And 2nd row Mat Date is 26-06-2022 which is recorded in Treasury Table and Availment Date is 01-05-22.

For 3rd row the next int reset date is 01-07-2022 ,from 2nd row we have still 3 days left in June 

So the 3rd row Mat Date is 30-06-22 and Vailement Date is 27-06-2022(Prior row Mat Date+1)
For 4th row the Mat Date is 31-Aug-22 because the next reset date is 01-09-2022 and Availment Date for this is 01-07-2022

For 5 th  row the Mat Date is 26-12-22 (is there in Treasury Table) (it's considered bcoz the Avialment for this one is 01-09-22)

How to achieve the solution for this..Can anyone help me out on this??

TIA

[likhithar]

@v-rongtiep-msft  I'm sharing the .pbix file via Drive Link

https://drive.google.com/file/d/137zHQgM4eA3oTy_TSIL9fofZqbDxB3eo/view?usp=sharing

And the desired output I'm attaching in the below snap

[v-rongtiep-msft]

Hi @likhithar ,

I don't think you really understand what I mean. How do you get the second column [Mat Date] in the output table that you want? It doesn't appear in your pbix file, except for the Treasury table and Int Rate table. there is a date table, is it calculated in the date table? Or is it in the Treasury table?Sorry, forgive my stupidity.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft]

Hi @likhithar ,

Please have a try.

Create a meausre.

Measure = var _intid=CALCULATE(MAX('int table'[Int Id]),FILTER(ALL('Treasury table'),'Treasury table'[Id]=SELECTEDVALUE('summarize Table'[ID])))
var _2=CALCULATE(MAX('int table'[Int reset date]),FILTER(ALL('int table'),'int table'[Int Id]=_intid&&'int table'[Int reset date]<SELECTEDVALUE('summarize Table'[Mat date])))
return _2

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[likhithar]

@v-rongtiep-msft  Thanks for the work!!!
But my scenario is the summarized table is my output I needed in my report,In my Data Modeling I will have only Int Table and Treasury Table and Date Table(Dimension Table)

[v-rongtiep-msft]

Hi @likhithar ,

If so, where is the data in the summarize table? For example , the Mat date and the id?  In your Date table((Dimension Table)? If so, you can use the above measure. If not , please provide more details with the desired output.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.