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

IDDateFruit NameTypePrice
541601/31/2020OrangeType-27
541611/31/2020Apple Type-38
541621/31/2020GrapesType-12
541631/31/2020OrangeType-15
541641/31/2020OrangeType-310
541651/31/2020Apple Type-13
541661/31/2020GrapesType-24
541671/31/2020GrapesType-36
541681/31/2020OrangeType-15
541691/31/2020OrangeType-27
541701/31/2020Apple Type-38
541711/31/2020GrapesType-12
541721/30/2020OrangeType-15
541731/30/2020OrangeType-310
541741/30/2020Apple Type-13
541751/30/2020GrapesType-24
541761/30/2020GrapesType-36
541771/30/2020OrangeType-15

 

Table-2

IDDate Sold on OR commentFruit NameType
5416402-02-2020OrangeType-3
5416503-02-2020Apple Type-1
5416704-02-2020GrapesType-3
5417104-02-2020GrapesType-1
54174Yet to be soldApple Type-1
54175didn't sold yetGrapesType-2
54177 OrangeType-1

 

Expected Output

OutputTotal
January Sales67
Febuary sales21

 

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.

6 Replies

  • 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

      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's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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