Creating a measure using multiple tables

[cass_butchard]

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!

[Anonymous]

Hi @cass_butchard ,

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

results?

Regards,

Xiaoxin Sheng

[Anonymous]

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]

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]

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]

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

 

[Anonymous]

HI @cass_butchard ,

Can you check if the link helps

Solved: Derive open defects from the past using Dax - Microsoft Fabric Community

Cheers
CheenuSing

[cass_butchard]

@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

[Anonymous]

Hi @cass_butchard ,

It is very difficult to simulate the data. If possible just share the data with only columns to be used in the visuals and your date table, then only can provide a solution.

Cheers

CheenuSing

[cass_butchard]

I have attached the pbix let me know if any issues @Anonymous 

Outstanding pothole defects EOM.pbix

[Anonymous]

Hi @cass_butchard ,

Unable to download pbix , as the link is pointing to sharepoint.

Put it in One Drive or Google Dirve and share the link here. 
My email id is srini@samiksha.com.sg

Cheers

CheenuSing

[Anonymous]

Hi @cass_butchard ,

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

Cheers

CheenuSIng

[cass_butchard]

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]

Hi @cass_butchard ,

Can you please share some more detail information about this scenario? They should help us clarify your scenario and test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[cass_butchard]

HI v-shex-msft - I have included additional information in my replies above what other information would you like me to include?

Thank you

[Anonymous]

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]

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: