Forum Discussion

sjehanzeb's avatar
sjehanzeb
Icon for Resolver I rankResolver I
5 years ago
Solved

Combining calendar table with calculated amount from another table in addition to customer id

This is in continuation of a Previous post.  My objective is to create a table with month wise running balance of individual customer. My transactional data is simply a list of transactions (plus/minus).

I created a calendar table with individual months so that balance still shows even if there is no transactions. my issue is that I am unable to add customer id to the summarize table. 

Calender = my own calender table that is linkedin to transactional table's date column
SF = Transactional Table
SF[Line Amt] = Transactions (plus / minus)

SF[ID] = Individual customer ID (cannot insert it here)

 

 

SUMMARIZE(Calender, Calender[Year Month], Calender[Year Month Number],  "Amount", CALCULATE(sum(SF[Line Amt]), FILTER(ALL(SF), RELATED(Calender[Year Month Number]) <= EARLIER(Calender[Year Month Number]))))

 

  • So, I resolved it. Combine two tables with all values

    var calendertable = SUMMARIZE(Calender, Calender[Year Month])
    var SFtable= SUMMARIZE(SF, SF[ID])
    return
    CROSSJOIN(calendertable, SFtable)

1 Reply

  • So, I resolved it. Combine two tables with all values

    var calendertable = SUMMARIZE(Calender, Calender[Year Month])
    var SFtable= SUMMARIZE(SF, SF[ID])
    return
    CROSSJOIN(calendertable, SFtable)