cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PrabodhPurwar
Microsoft
Microsoft

Calculate the previous data

Hello Team ,

 

I want to calculate the previous data before the time period 

 

iddatestatus         
11-Mayopen         
130-Aprclosed    start dateend date  Prev open
12-Mayopen    4-Jul14-Jul  1
14-Julclosed         
6 REPLIES 6
Anonymous
Not applicable

 

 

 

[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
Not applicable

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

I want to dynamically count distinct IDs with latest AuditCreated dates and other column filters.
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
Not applicable

@ashwini12 

 

OK, what's wrong with my measure above?

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

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

 

Condition 1:

Slicer 

3-May14-Jul

 

 

i need the data before 3 May   

 

Condition 2: 

4-Jul14-Jul

 

 

Need data before 4 july 

 

 

ddatestatus         
11-Mayopen         
130-Aprclosed         
12-Mayopen         
14-Julclosed 

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!