Forum Discussion
Roll Forward Calculation
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' |
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.
3 Replies
Not very clear.
But refer to: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Or check this file : https://www.dropbox.com/s/97ff7141d5atppy/daysbetweendates.pbix?dl=0
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'...
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.
