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 ways of writing this however although I do return some results the correct number of results are not being returned.  
 
Essentially I have a "defects table" and a "date" table and I want to sum a quantity field "Estimated Quantity" for all defects which were not completed at the end of a month period.  So where the "completed date" field is blank at the end of a month OR the completion date was entered in a later period so at the previous month it was still outstanding. 
 
My measure is as follows:
OutstandingDefectsEOM = CALCULATE(
       SUM(Defects[Estimatedquantity]),
       FILTER(
        GENERATE(Defects, 'Date'),
        Defects[CompletionDate(ShortDate)] > 'Date'[MonthEnd] || Defects[CompletionDate(ShortDate)] = BLANK()
        ))
 
Using December 2024 as an example it is returning 613 HOWEVER I have run a report from the system using this same logic and have a result of 1006. 
 

 

 
I am completely stuck and have run out of ideas to try to resolve this. Please HELP!
Need Help

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cass_butchard ,

     

    Try the following measure

    It is assumed that there is a relationship between Defects and Date table.

     

    Oustnding DefectsEOM = CALCULATE(
           SUM(Defects[Estimatedquantity]),
           FILTER( Defects,
                   Defects[CompletionDate(ShortDate)] > 'Date'[MonthEnd] || Defects[CompletionDate(ShortDate)] = BLANK()
            ))
     
    Let me know if it worked.
     
    Cheers
     
    CheenuSing
    • cass_butchard's avatar
      cass_butchard
      Frequent Visitor
      1 year ago

      Hi There,

      I have tried the above solution however even though there is a relationship between the date table and defect table I am unable to use a field from the date table in the filter statement without the generate statement connecting the two tables together.  Screen shots below:

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cass_butchard ,

     

    X-Axis is it populated from Date table or Defects Table ?

     

    Cheers

     

    CheenuSIng

    • cass_butchard's avatar
      cass_butchard
      Frequent Visitor
      1 year ago

      Hi CheenuSing - The X Axis populated from the date raised field in defects however I just attempted to use a date field from the Date table on the X axis and this still did not allow me to populate this field

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cass_butchard ,

     

    When typing the DATE([MonthEnd]), try typeing 'Date'[MonthEnd] instaead of DATE[MonthEnd].

     

    Reason being DATE when typed is a function. WHen Typed 'DATE'  it will refer to the date table. 
    Alternatively change the table name Date to DimDate and try.

     

    Cheers

     

    CheenuSing

    • 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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cass_butchard ,

    Any update on this? Can you please share some more detail information about this?

    results?

    Regards,

    Xiaoxin Sheng