Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello People,
I two tables as follows
Table No.1
Books of Accounts (My records) | ||
Date | Invoice No. | Tax Credit |
01-04-2020 | 04202001 | 8,085 |
15-04-2020 | 04202002 | 3,131 |
20-04-2020 | 04202003 | 7,300 |
25-04-2020 | 04202004 | 8,810 |
07-05-2020 | 05202001 | 5,580 |
13-05-2020 | 05202002 | 9,690 |
27-05-2020 | 05202003 | 4,462 |
03-06-2020 | 06202001 | 9,506 |
08-06-2020 | 06202002 | 7,259 |
15-06-2020 | 06202003 | 9,421 |
21-06-2020 | 06202004 | 4,740 |
Table No.2
Statutory records (Govt. Records) | ||
Date | Invoice No. | Tax Credit |
01-04-2020 | 04202001 | 8,085 |
15-04-2020 | 04202002 | 3,131 |
20-04-2020 | 04202003 | 7,300 |
07-05-2020 | 05202001 | 5,580 |
13-05-2020 | 05202002 | 9,690 |
27-05-2020 | 05202003 | 4,462 |
03-06-2020 | 06202001 | 9,506 |
08-06-2020 | 06202002 | 7,259 |
15-06-2020 | 06202003 | 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 Master | Invoice Date |
04202001 | 01-04-2020 |
04202002 | 15-04-2020 |
04202003 | 20-04-2020 |
04202004 | 25-04-2020 |
05202001 | 07-05-2020 |
05202002 | 13-05-2020 |
05202003 | 27-05-2020 |
06202001 | 03-06-2020 |
06202002 | 08-06-2020 |
06202003 | 15-06-2020 |
06202004 | 21-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 Master | Tax(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) | Difference | Ineligible 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 Master | Tax(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
Solved! Go to 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.
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 Master | Tax(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)
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.
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.
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 Master | Tax(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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |