Forum Discussion

siewwei's avatar
siewwei
Frequent Visitor
2 years ago

Dynamic table based on slicer

I have multiple tables, eg Sales table, Billing table, CN table, Receipt table where each of the table having a date field. Next, I have a date slicer. I want to allow user to select the date range a...
Need Help
siewwei's avatar
siewwei
Frequent Visitor
2 years ago

in Sales table i have CustomerID, then Sales table link to Billing, Collection and Credit Note table with Customer ID. in Billing it will have all the information about billing like billing date, document no and amount. in Collection it will have all information about collection like collection date, document no and amount. Same to Credit Note table. that's the reason i use Sales Table as the main table to link Sales, Collection and Credit Note.

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello siewwei ,

     

    then create a dim customer that has the customer info without duplication, and link this table to all other table through customer id. thus they will be all linked and you can link the date table to all of them as well.

    • siewwei's avatar
      siewwei
      Frequent Visitor
      2 years ago

      Actually CustomerID (DebtorID) in Sales table is unique, no duplication. however, the value doesnt change according the date range selected. It gives me the total amount.

       
      new Total Receipt =
      VAR _startdate = MIN('Calendar Table'[Date])  // Get the start date of the range
      VAR _enddate = MAX('Calendar Table'[Date])    // Get the end date of the range
      RETURN
          SUMX(
              FILTER(
                  IFCA_CB_Receipt,
                  IFCA_CB_Receipt[DebtorID] = IFCA_PS_Sale[DebtorID] &&
                  NOT(ISBLANK(IFCA_CB_Receipt[DocNo])) &&
                  IFCA_CB_Receipt[DocumentDate] >= _startdate && IFCA_CB_Receipt[DocumentDate] <= _enddate
              ),
              IFCA_CB_Receipt[DocumentAmt]
          )