Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
coolshib
Helper III
Helper III

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

 

 

 

1 ACCEPTED SOLUTION

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.

 

2020-08-26 06_48_41-scratch4 - Power BI Desktop.png

View solution in original post

7 REPLIES 7
dedelman_clng
Community Champion
Community Champion

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

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

 

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) 

 

2020-08-25 13_36_00-scratch3 - Power BI Desktop.png

 

Hope this helps

David

Dear @dedelman_clng Thank you so much for your reply.

Yes, you are right.. the matched entries are those entries which are reflecting in both the measures.

The measure is not returing the desired result in report.

Sharing the screenshot of the report for your reference.

 

DAX.JPG

 

 

 

 

 

Could you please share your pbix file for my reference?

Many thanks.

Best Regards,

Shib

 

 

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.

 

2020-08-26 06_48_41-scratch4 - Power BI Desktop.png

@dedelman_clng Thank you so much.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.