Forum Discussion

coolshib's avatar
coolshib
Icon for Helper III rankHelper III
6 years ago
Solved

Need help on logic based DAX measure

Hello People,

I two tables as follows

Table No.1

Books of Accounts (My records)
DateInvoice No.Tax Credit
01-04-202004202001       8,085
15-04-202004202002       3,131
20-04-202004202003       7,300
25-04-202004202004       8,810
07-05-202005202001       5,580
13-05-202005202002       9,690
27-05-202005202003       4,462
03-06-202006202001       9,506
08-06-202006202002       7,259
15-06-202006202003       9,421
21-06-202006202004       4,740

 

Table No.2

Statutory records (Govt. Records)
DateInvoice No.Tax Credit
01-04-202004202001       8,085
15-04-202004202002       3,131
20-04-202004202003       7,300
07-05-202005202001       5,580
13-05-202005202002       9,690
27-05-202005202003       4,462
03-06-202006202001       9,506
08-06-202006202002       7,259
15-06-202006202003       9,421

 

Table No.3

Date Table

Date Table
01-04-2020

upto 31-12-2020

 

I have created a new table using power query from Table No.1 as follows

Table No.4

Invoice MasterInvoice Date
0420200101-04-2020
0420200215-04-2020
0420200320-04-2020
0420200425-04-2020
0520200107-05-2020
0520200213-05-2020
0520200327-05-2020
0620200103-06-2020
0620200208-06-2020
0620200315-06-2020
0620200421-06-2020

 

I have linked the Master Invoice column from Table no.4 with Table No.1 & Table No.2

I have also linked the date column from date table with Table no.4.

I have created two measures using calculte & sum as [Tax(Books of Accounts)] and [Tax(Statutory)].

I have created another measure as [difference] which is difference between [Tax(Books of Accounts)] and [Tax(Statutory)].

 

My Invoice wise report appears as follows

Invoice MasterTax(Books of Accounts)Tax(Statutory)Difference
04202001                8,085          8,085               -  
04202002                3,131          3,131               -  
04202003                7,300          7,300               -  
04202004                8,810                 -          8,810
05202001                5,580          5,580               -  
05202002                9,690          9,690               -  
05202003                4,462          4,462               -  
06202001                9,506          9,506               -  
06202002                7,259          7,259               -  
06202003                9,421          9,421               -  
06202004                4,740                 -          4,740

 

I am looking for a  new measure as [Ineligible Credit] based on a criteria which would return the value as shown in table below.

The criteria is:

if the difference is less 20% of the monthwise total matched entries of Tax(Books of Accounts) then the value would be Zero other wise the value would be :

[difference] Less [sum of total mothwise matched Tax(Books of Accounts)]*20%.

 

Please note that i have not created the [sum of all matched entries] measure.

 

 Tax(Books of Accounts)Tax(Statutory)DifferenceIneligible Credit
Apr-20              27,326        18,516        8,810          5,107
May-20              19,732        19,732               -                   -  
Jun-20              30,926        26,186        4,740                 -  

 

As per my example dataset in the month of April 2020 there 4 entries as follows

 

Invoice MasterTax(Books of Accounts)Tax(Statutory)Difference
04202001                8,085          8,085               -  
04202002                3,131          3,131               -  
04202003                7,300          7,300               -  
04202004                8,810                 -          8,810

 

The total sum of matched entries is 18516 and 20% of 18516 is 3703.

So the Eligible amount would be 3703 and Ineliigible amount would be 8810-3703 = 5107

 

Power BI desktop file for your reference 

 

Hope i have explained my query properly.

Many thanks in advance.

Best Regards,

Shib

 

 

 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    Hi coolshib  - 

     

    I have attached my pbix.  Please note that if you want to display this measure at the invoice/date level, you are going to get different results than at the month level.

     

7 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi coolshib  - 

     

    The DAX formula you would need would be

     

    Ineligible Credit =
VAR __diff = [Tax_Diff]
VAR __TaxSum20 = ( [Tax_BoA] + [Tax_Statutory] ) * 0.2
RETURN
    IF ( __diff < __TaxSum20, 0, __diff - __TaxSum20 )

     

    However, please realize that with the sample data you shared, Tax_Diff for April is 19.3% of Tax_BoA + Tax_Statutory, so you will get 0s for all three months. If this is not correct, pleaser re-state your logical conditions.

     

    Hope this helps

    David

    • coolshib's avatar
      coolshib
      Icon for Helper III rankHelper III
      6 years ago

      Dear dedelman_clng Thank you so much for your help.

      I need to correct myself on the logic part,

      if the difference is less 20% of the total matched entries of Tax(Books of Accounts)  then the value would be Zero other wise the value would be
      [difference] Less [sum of matched Tax(Books of Accounts)]*20%.

      As per my example dataset in the month of April 2020 there 4 entries as follows

       

      Invoice MasterTax(Books of Accounts)Tax(Statutory)Difference
      04202001                8,085          8,085               -  
      04202002                3,131          3,131               -  
      04202003                7,300          7,300               -  
      04202004                8,810                 -          8,810

       

      The total sum of matched entries is 18516 and 20% of 18516 is 3703.

      So the Eligible amount would be 3703 and Ineliigible amount would be 8810-3704 = 5107

       

      Hope i have explained my query properly.

      Many Thanks once again for your help.

      Best Regards,

      Shib

    • coolshib's avatar
      coolshib
      Icon for Helper III rankHelper III
      6 years ago

      Dear dedelman_clng Thank you so much for your help.

      I need to correct my logic further which is as follows

      The criteria is:

      if the difference is less 20% of the monthwise total matched entries of Tax(Books of Accounts) then the value would be Zero other wise the value would be :

      [difference] Less [sum of total mothwise matched Tax(Books of Accounts)]*20%.

       

      As per my example dataset in the month of April 2020 there 4 entries as follows

       

      Invoice MasterTax(Books of Accounts)Tax(Statutory)Difference
      04202001                8,085          8,085               -  
      04202002                3,131          3,131               -  
      04202003                7,300          7,300               -  
      04202004                8,810                 -          8,810

       

      The total sum of matched entries is 18516 and 20% of 18516 is 3703.

      So the Eligible amount would be 3703 and Ineligible amount would be 8810-3703 = 5107.

      Hope i explained my requirment properly.

      Many thanks for your help.

      Best Regards,

      Shib

       

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi coolshib  - are "matched entries" referring to invoices that have both BoA tax and Stautory tax?  So the total of "matched entries" would be the total of the Statutory amounts?

         

        In that case, 

         

        Ineligible Credit = var __diff = [Tax_Diff]
var __TaxSum20 = [Tax_Statutory] * 0.2
return 
IF (__diff < __TaxSum20, 0, __diff - __TaxSum20)

         

         

        Hope this helps

        David