Forum Discussion

saranp780's avatar
saranp780
Helper III
8 years ago
Solved

PO Report with (+-) condition

I have a suite of example data below. That  I got for the system.

 

Purch.Doc.ItemMatYrPstng Date    Quantity   Amount in LCCrcyD/C
10200000021201728.08.201715027,750.00USDS
10200000021201728.08.201715027,750.00USDH
10200000021201701.08.201715027,750.00USDS
10200000031201805.01.201820018,000USDS
10200000032201805.01.201830021,300USDS
10200000031201805.04.2018504,500USDH
 1020000003 1 201808.04.2018252,250USDS
Please Note SDebit     
 HCredit     

 

Now, What I want to do is make a cumulative report using Debit and Credit to calculate. 

 

Just so you know about this data.

Posting date means the day of PO makes that move.

D/C Means Debit/Credit in the system it uses S instead Debit and H instead debit.

And when H the amount will be decreased, if S the amount will be Increased.

 

From my example data, the result will be like this.

Year 2017             
Purch.Doc.JanFebMarAprMayJunJulAugSep OctNovDecTotal
1020000002       27,750.00    

27,750

(27,750-27,750+27,750)

              
Year 2018             
102000000339,300  4,5002,250       

37,050

(39,300-4,500+2,250)

 

Please help to tell me the DAX that I can use to solve my requirement.

 

Thank you all.

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi saranp780,

    Please follow the steps below.

    1. Create a calendar table clicking 'New Table' under Modeling on Home page, type the formula. Create a month column.



    Calendar = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))
    Column = FORMAT('Calendar'[Date],"MMM")


    2. Create a relationship between your fact table(1) and Calendar table(n).



    3. Create a calculated column using the formula.

    Amount_new = IF(Table2[D/C]="S",Table2[   Amount in LC],-Table2[   Amount in LC])



    4. Create a matrix, select MatYr and Purch.Doc as rows level, the Month as column, then click the button highlighted in yellow, you will get the right result.


    Best Regards,
    Angelia

1 Reply

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi saranp780,

    Please follow the steps below.

    1. Create a calendar table clicking 'New Table' under Modeling on Home page, type the formula. Create a month column.



    Calendar = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))
    Column = FORMAT('Calendar'[Date],"MMM")


    2. Create a relationship between your fact table(1) and Calendar table(n).



    3. Create a calculated column using the formula.

    Amount_new = IF(Table2[D/C]="S",Table2[   Amount in LC],-Table2[   Amount in LC])



    4. Create a matrix, select MatYr and Purch.Doc as rows level, the Month as column, then click the button highlighted in yellow, you will get the right result.


    Best Regards,
    Angelia