Forum Discussion
Dynamic start date filter (Powerpivot)
I simply cant figure out how to create a dynamic measure, that only counts start dates, where there is at lapse of at least 3 months since the last enddate. I have a table with customers, that al...
Hi Abduvali,
Not exactly. Last enddate has to be more than 3 month prior to next startdate.
I have given a couple of examples in the linked file. For instance Customer6:
There is a timelapse of 83 days (less than 3 months) between first "CaseDateEnd" (B) date and next "CaseDateStart" (A).
Therefore the measure shoulde only count the first "CaseDateStart" (10-05-2017) and not the second.
My problem is that i want a measure that can count start dates, but i want to filter the start dates, based on data from another row and column.....
Hopes it makes sense.
I think I got it to work using IF statements using 2 additional columns:
- this checks for the newest Start Date
- New Start Date = if((Sheet6[New Date]-Sheet6[End])>=89,Sheet6[New Date],Sheet6[Start])
- And the second column will let you count the dates that are greater than 3 month
- Count Dates = if((if(Sheet6[End]>Sheet6[New Start Date],0,DATEDIFF(Sheet6[End],Sheet6[New Start Date],DAY)))=0,0,1)
Check it out and see how you get on maybe someone will propose a better solution.
Regards
Abduvali