Forum Discussion

cass_butchard's avatar
cass_butchard
Frequent Visitor
1 year ago

Creating a measure using multiple tables

Hi There,   I am new to this forum and require some assistance in creating a measure that displays the sum of a column which has been filtered by certain criteria.  However I have tried numerous wa...
Need Help
cass_butchard's avatar
cass_butchard
Frequent Visitor
1 year ago

Hi Anonymous - MonthEnd is a field within my own date table that I am attempting to use to calculate in order to sum at a moment in time being the last day of the month - so if the completion date is after that month has finished then it would have been outstanding for that particular month and also count fields that were BLANK for that month also. 

I tried both of your options but I need the generate statement to link both the Defects Table and the Date table together.  I am also using date raised in the X axis.  My current measure is:

OutstandingDefectsEOM = CALCULATE(
       SUM(Defects[Estimatedquantity]),
       FILTER(
         GENERATE(Defects, 'Date'),
         OR(Defects[CompletionDate(ShortDate)] > 'Date'[MonthEnd], Defects[CompletionDate(ShortDate)] = BLANK())
        ))
which is returning a value of 613 however based on records in the system it should be 1006.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi cass_butchard ,

 

Can you please share the pbix or the data of defects and date in onedrive and share the link.

 

Can you paste the data model from the model view.

 

Cheers

 

CheenuSing

 

  • cass_butchard's avatar
    cass_butchard
    Frequent Visitor
    1 year ago

    Hi, Unfortunately I cannot provide access to the pbix due to our internal security requirements.. However I have included some print screens below of the Data Model - Please let me now if you require additional information

     

     

     

     

     

     

      • cass_butchard's avatar
        cass_butchard
        Frequent Visitor
        1 year ago

        Anonymous - Unfortunately I have re-created the above and it gives me the exact same figures as my original measure - Which is not the correct number is it does not align to the figures from the system.  The only thing I can consider is amending the date field used in the axis as it changes the figures when I use Date Raised or Completion Date but when I use a standard date field the numbers are the same as date raised. I really appreciate all your time and assistance in this matter. 

        The correct figure for this should be 996