Roll Forward Calculation

[codyraptor]

I have the following data:
Vendor ID, Load Date, Expiration Date.  I need a distinct count of vendor ID's.  New = 'min load date' where expiration date null or > than selected month.  Exp is 'max exp date' where expiration date is <= selected month.

I've tried a summarize function...generate a table.  I can do this in SQL..but I think I'm overthinking it with Dax.

Vendor	Load Date	Exp Date		Expected Results…"Distinct Count of Vendor":
1	Jan-20				Start	New	Exp	End
1	Feb-20	Mar-20		Jan	3	3	0	3
2	Jan-20			Feb	3	0	0	3
3	Mar-20	Apr-20		Mar	3	1	0	4
4	Jan-20	Jan-20		Apr	4	0	2	2
4	Mar-20	Apr-20				New = 'min load date'
4	Apr-20	Apr-20				Exp = 'max exp date'

[amitchandak]

Not very clear.

But refer to: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or check this file : https://www.dropbox.com/s/97ff7141d5atppy/daysbetweendates.pbix?dl=0

[codyraptor]

This is great information.  However...I'm running into 1 snag.  My rows are not unique 1 load date..and or 1 expiration date.

Another words...
Vendor '123' could have a load date of Jan & Feb & etc..   The 'min date grouped by Ven 123' would represent their 'start date'

Vendor '123' could have a 'blank' and an expiration date.  I only want to count the expiration date if a null doesn't not exist on any row for that vendor id.  If blank exists..then it's not truly expired.   In the case 'all rows' are expired for the VEN 123..then I want the 'max expiration date'...

[v-yuta-msft · Accepted solution]

@codyraptor ,

I'm afraid dynamic calculate table is not supported in power bi, so you couldn't generate such a table based on the slicer selection.

Community Support Team _ Jimmy Tao

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