Forum Discussion

PrabodhPurwar's avatar
PrabodhPurwar
Microsoft Employee
5 years ago

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         
DAX Commands and Tips

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • PrabodhPurwar's avatar
      PrabodhPurwar
      Microsoft Employee
      5 years ago

      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 

       

       

    • ashwini12's avatar
      ashwini12
      Microsoft Employee
      5 years ago
      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        ashwini12 

         

        OK, what's wrong with my measure above?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

     

    [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!