i have a date level granularity table , it has something called indicator stating if it had a missing date for a session. the requirment i have is currently looking to aggregate the missing value using concatenation.. depending on date range provided .. Some user can provide a day level , some can provide week date range and some can provide monthly or even longer date
Thi is how my table looks ( but with more deeper granularity ). Missing Date is only filled if i have missed a session in that particular day , if i haven't missed it , it shows null.
| Session ID | Session State ID | Session Date | Session Minutes | Session Missed Dates |
| 976 | 5820 | 9/1/2016 | 30 | null |
| 976 | 5820 | 9/2/2016 | 30 | null |
| 976 | 5820 | 9/3/2016 | 30 | null |
| 976 | 5820 | 9/8/2016 | 0 | null |
| 976 | 5820 | 9/7/2019 | 30 | null |
| 976 | 5820 | 9/16/2016 | 30 | 9/16/2016 |
| 976 | 5820 | 9/19/2016 | 30 | 9/19/2016 |
| 976 | 5820 | 9/17/2016 | 45 | null |
| 976 | 5820 | 9/15/2016 | 30 | null |
| 976 | 5820 | 9/22/2016 | 30 | null |
| 976 | 5820 | 9/9/2016 | 45 | null |
| 976 | 5820 | 9/20/2016 | 30 | 9/20/2016 |
| 976 | 5820 | 9/21/2016 | 30 | 9/21/2016 |
| 976 | 5820 | 9/28/2016 | 45 | null |
| 976 | 5820 | 9/4/2016 | 45 | 9/4/2016 |
| 976 | 5820 | 9/5/2016 | 45 | 9/5/2016 |
| 976 | 5820 | 9/6/2016 | 45 | 9/6/2016 |
| 976 | 5820 | 9/12/2016 | 45 | 9/12/2016 |
| 976 | 5820 | 10/3/2016 | 45 | 10/3/2016 |
| 976 | 5820 | 10/4/2016 | 45 | 10/4/2016 |
| 976 | 5820 | 10/5/2016 | 45 | 10/5/2016 |
Using Power Query , i can concatenate the table with data with missing date aggregating either at a month or weekly level. no issues doing their. So essentially missing date
assuming i selected week 10/04/2016 to 10/10/2016 my result set should show
exactly same thing but in a row and display missing dates 10/04, 10/05/
if selected october month then my missing date column should display 10/03, 10/04, 10/5... if i select year then it should include dates.. the concatenation of the dates should be dynamic in nature..
I am looking for some run time dax for this kind of situation as i do not know the dates the user would select
it could be single day , mid week , weekly , monthly or yearly or any custom date range .. So missing date should fit the values between start and end date of the date range selected by user.
Any suggesiton on how should i be approaching this ..
thoughts
