Considering only date from another table to sum

[labuser1235]

Hi All,

I have two tables here where Table-1 contains entire data and Table-2 contains only odd once out. 

In Table-2 column Date Sold on OR comment I only want those rows with date and ignore the rest with text or blank to get compared with Table-1 so that I can sum it to find the individual months and dates data. 

Table-1

ID	Date	Fruit Name	Type	Price
54160	1/31/2020	Orange	Type-2	7
54161	1/31/2020	Apple	Type-3	8
54162	1/31/2020	Grapes	Type-1	2
54163	1/31/2020	Orange	Type-1	5
54164	1/31/2020	Orange	Type-3	10
54165	1/31/2020	Apple	Type-1	3
54166	1/31/2020	Grapes	Type-2	4
54167	1/31/2020	Grapes	Type-3	6
54168	1/31/2020	Orange	Type-1	5
54169	1/31/2020	Orange	Type-2	7
54170	1/31/2020	Apple	Type-3	8
54171	1/31/2020	Grapes	Type-1	2
54172	1/30/2020	Orange	Type-1	5
54173	1/30/2020	Orange	Type-3	10
54174	1/30/2020	Apple	Type-1	3
54175	1/30/2020	Grapes	Type-2	4
54176	1/30/2020	Grapes	Type-3	6
54177	1/30/2020	Orange	Type-1	5

Table-2

ID	Date Sold on OR comment	Fruit Name	Type
54164	02-02-2020	Orange	Type-3
54165	03-02-2020	Apple	Type-1
54167	04-02-2020	Grapes	Type-3
54171	04-02-2020	Grapes	Type-1
54174	Yet to be sold	Apple	Type-1
54175	didn't sold yet	Grapes	Type-2
54177		Orange	Type-1

Expected Output

Output	Total
January Sales	67
Febuary sales	21

[v-easonf-msft]

Hi , @labuser1235 

Try steps as below:

1.create calculate column in table-2 and change the type as date

Date Sold on OR Comment2 = IF('Table-2'[Date Sold on OR comment]="Yet to be sold"||'Table-2'[Date Sold on OR comment]="didn't sold yet"||'Table-2'[Date Sold on OR comment]="",BLANK(),'Table-2'[Date Sold on OR comment])

2. create measure "Price Amout" to instead of field "price " ,and put it into value of matrix 

Price Amount = 
CALCULATE (
    SUM ( 'Table-1'[Price] ),
    FILTER (
        'Table-1',
        VAR i = [ID]
        RETURN
          IF (
                CALCULATE (
                    COUNTROWS ( 'Table-2' ),
                    FILTER('Table-2','Table-2'[ID] = i
                        && ISBLANK ( 'Table-2'[Date Sold on OR Comment2] ))
                ) > 0,
                FALSE (),
                TRUE ()
            )
    )
)

Here is a demo.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[labuser1235]

[labuser1235]

@v-easonf-msft Thanks for getting back. However, the solution provided is partial correct as it does not consider as per the dates. 

Dates from Table-2 has to be considered for Table-1 only for those sepcific IDs. As per your month column in Table-1 your data shows from two months. However, in the data I have given there no second month data in Table-1. 

Only Table-2 contains 2nd month dates which has to be considered.

Even though the I select the Table-2 date it still shows as that is from month 1. 

Under Date Sold on Or Comment2 calculated column IF condition has 3 OR conditions as per the data. However, in reality there could be many more other text values as well, is there any dynamic conditions which picks so that too many OR conditions are not passed. Please check

[v-easonf-msft]

Hi , @labuser1235 

Sorry. I'm still a little confused about your first question.Can your tell me more details .

Does the graph you show indicate that you want this sale to be counted in the April sale(Table-2 date sold on OR date) instead of the January sale(Table-1 Date)?

For your last question you can change the formula as below:

Date Sold on OR Comment2 = IF(IFERROR(DATEVALUE([Date Sold on OR comment]),BLANK()),[Date Sold on OR comment])

 

Best Regards,
Community Support Team _ Eason

[labuser1235]

@v-easonf-msft thanks for getting back. 

In the data I provided, Table-1 data was completely on January and Table-2 was completely on Febuary and there are no other months I have provided. The date format I have provided in Table-1 is MM/DD/YYYY and the date format for Table-2 is DD-MM-YYYY my bad that I haven't mentioned it correctly. 

Table-1 data is based on January. However, As per the ID in table-2, only those IDs were sold in the Month of Febuary but not in January. Your data taken into consideration was wrong in Table-1 (As you have 2nd month which I haven't provided in Table-1 source) please recheck the data as I have provided in my first post.

Meaning it has to ignore those IDs which are from table-2 in Table-1, and consider those dates from table-2 as those are the latest and provide the sum based on the latest dates from Table-2. Please send the PBIX file once done so that I can check and get back to you on that.

I hope I am clear now.  🙂

[v-easonf-msft · Accepted solution]

Hello , @labuser1235

Here we go.

1.In Edit Queries, add the custom column "true date" in Table 2 and change the date type

Custom add:

= Table.AddColumn(#"Changed Type with Locale", "True Date", each if Text.Contains([Date Sold on OR comment],"-") then [Date Sold on OR comment] else null)

Modified type1:

= Table.TransformColumnTypes(#"Added Custom",{{"True Date", type date}},"Ar-BH")

It is shown as shown below:

2.Add a calendar table

CalendarTable = CALENDAR(MIN(MIN('Table-1'[Date]),MIN('Table-2'[True Date])), MAX(MAX('Table-1'[Date]),Max('Table-2'[True Date])))

3.Change the formula "price quantity" in table-1 as follows:

Price Amount = 
CALCULATE (
    SUM ( 'Table-1'[Price] ),
    FILTER (
        'Table-1',
        VAR i = [ID]
        RETURN
          IF (or(
                CALCULATE (
                    COUNTROWS ( 'Table-2' ),
                    FILTER('Table-2','Table-2'[ID] = i
                           )
                ) > 0 , not( 'Table-1'[Date] in DISTINCT('CalendarTable'[Date]))) ,
                FALSE (),
                TRUE ()
            )
    )
)+CALCULATE(SUMX(FILTER('Table-2','Table-2'[True Date] in DISTINCT('CalendarTable'[Date])),CALCULATE(SUM('Table-1'[Price]),'Table-1'[ID] = EARLIER('Table-2'[ID]))))

...

Here's a demo.

Best regards
Community Support Team _ Eason
If this post helps,then please consider Accepting it as the solution to help the other members find it more quickly.