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