Calculate the previous data

[PrabodhPurwar]

Hello Team ,

I want to calculate the previous data before the time period 

id	date	status
1	1-May	open
1	30-Apr	closed					start date	end date			Prev open
1	2-May	open					4-Jul	14-Jul			1
1	4-Jul	closed

[Anonymous]

[ID Count (Open Before)] =
// There must be a field in the
// table T that is a true date
// not a string. Assume the
// field is called date. Then:
var MinDateSelected = MIN( T[date] )
return
	CALCULATE(
		DISTINCTCOUNT( T[id] ),
		KEEPFILTERS( T[status] = "open" ),
		T[date] < MinDateSelected
	)

// Note well that you should not have
// models with one table only but 
// build a star schema. If you don't
// do this, you are risking creating
// a lot of issues, one of them being
// correct measures that produce incorrect
// results. You've been warned!

[Anonymous]

Can you please be more specific? It's not entirely clear what you want to achieve. Thanks.

[ashwini12]

I want to dynamically count distinct IDs with latest AuditCreated dates and other column filters.

yesterday

var maxValue =

CALCULATE(

MAX('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate]),

ALLEXCEPT('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[Id]

),

DATESBETWEEN('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate],[Start Of Previous Period],[End Of Previous Period]),

DATESBETWEEN(Dim_Date[Date],[Start Of Previous Period],[End Of Previous Period]),

'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 3 || 'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 4 ,

'View_AllHelpToolRequestsWithAudit (2)'[AuditIsActive] = TRUE()

)

VAR colmax= MAXX('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate])

return

COUNTX(FILTER(VALUES('View_AllHelpToolRequestsWithAudit (2)'[Id]),maxValue <=colmax),'
View_AllHelpToolRequestsWithAudit (2)'[Id])

The above measure is not working as other filters  along with ALL Except are not working . Is there any other way to filter the table by above column filters and the Group by ID ?
----------------------------------------------------------------------------------------------------------------

Consider below scenario:
slicer - 12 june to 12 july

We need to check all records before 12 June (created a measure for this - between [start of previous period] and [end of previous period])

ID  AuditCreatedDate Status IsActive
1     12 June                 open    True  {should not consider this record as AuditedCreatedDate should be before 12 June }
1     11 June                 open     True ---- max (+1)
1     10 June                 close     True
2     11 June                 close     True         {should not consider this record as status is close}
2      10 June                open     True
2      9 June                  close     False
3      13June                 open     True {should not consider this record as AuditedCreatedDate should be before 12 June }

3      10 June                open     True -- ---max(+1)
3      6 June                  open     False

Ans: Previous Open ticket count = 2

[ashwini12]

var maxValue =

CALCULATE(

MAX('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate]),

ALLEXCEPT('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[Id]

),

DATESBETWEEN('View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate],[Start Of Previous Period],[End Of Previous Period]),

DATESBETWEEN(Dim_Date[Date],[Start Of Previous Period],[End Of Previous Period]),

'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 3 || 'View_AllHelpToolRequestsWithAudit (2)'[AuditNewStatusId] <> 4 ,

'View_AllHelpToolRequestsWithAudit (2)'[AuditIsActive] = TRUE()

)

VAR colmax= MAXX('View_AllHelpToolRequestsWithAudit (2)','View_AllHelpToolRequestsWithAudit (2)'[AuditCreatedDate])

return

COUNTX(FILTER(VALUES('View_AllHelpToolRequestsWithAudit (2)'[Id]),maxValue <=colmax),'
View_AllHelpToolRequestsWithAudit (2)'[Id])

The above measure is not working as other filters  along with ALL Except are not working . Is there any other way to filter the table by above column filters and the Group by ID ?
----------------------------------------------------------------------------------------------------------------

Consider below scenario:
slicer - 12 june to 12 july

We need to check all records before 12 June (created a measure for this - between [start of previous period] and [end of previous period])

ID  AuditCreatedDate Status IsActive
1     12 June                 open    True  {should not consider this record as AuditedCreatedDate should be before 12 June }
1     11 June                 open     True ---- max (+1)
1     10 June                 close     True
2     11 June                 close     True         {should not consider this record as status is close}
2      10 June                open     True
2      9 June                  close     False
3      13June                 open     True {should not consider this record as AuditedCreatedDate should be before 12 June }

3      10 June                open     True -- ---max(+1)
3      6 June                  open     False

Ans: Previous Open ticket count = 2

[Anonymous]

@ashwini12 

OK, what's wrong with my measure above?

[ashwini12]

@Anonymous There is nothing wrong with your measure. Its just that  requirement does not match with your measure output.

[PrabodhPurwar]

I want to count of Id's of Previous date where  latest status is open and the slicer is changing 

Condition 1:

Slicer 

3-May

14-Jul

i need the data before 3 May   

Condition 2: 

4-Jul

14-Jul

Need data before 4 july 

d	date	status
1	1-May	open
1	30-Apr	closed
1	2-May	open
1	4-Jul	closed