Forum Discussion

labuser1235's avatar
labuser1235
Icon for Helper IV rankHelper IV
6 years ago
Solved

Considering only date from another table to sum

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 t...
Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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:

    96.png

    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.

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
labuser1235
Icon for Helper IV rankHelper IV
6 years ago