Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PrabodhPurwar
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors