Quarterly Tax Payment Date For Cash Flow

[emre34]

Dear,

I have a measure where i have calculated my taxes upon budgetted turnover, 

Budget_Tax_Amount = 
var Company1_Tax = SUMX(PLANS, [Budget Total Sales] * [CIT_Rates_by_Branch])
var Company2_Tax = SUMX(BUDGET_COMPANY2_SALES, BUDGET_COMPANY2_SALES[Budget Sales USD Amount] * RELATED(BRANCH[Tax Rates.CIT Rate]) )
var Company3_Tax = SUMX(BUDGET_COMPANY3_SALES, [Budget_Company3_Sales_USD] * RELATED(BRANCH[Tax Rates.CIT Rate]) )
return Company1_Tax + Company2_Tax + Company3_tax

I have a  dates table and all of these 3 sales budget tables get related by date table. This measure is working good and showing all the budget numbers correctly. The problem is with my cash flow budgetting. I pay taxes quarterly and each quarter tax paying following second month. for example Q1 tax paying on 15/05/2024. 

I need a measure where it will be make a sum of quarterly amount and show it following second of month. how can i manupulate the date and provide it to show in requested date in the visuals (matrix , table).

thank you in advance.

[DataNinja777 · Accepted solution]

Hello @emre34 ,

Regarding the tax cash out flow which is 1.5 months later than your quarter end date, one solution is to create a calculated column in your sales fact table to indicate that if the sales happen during Q1, it will be paid on 15/05/2024 and so on.  I created a simple dummy data for illustration.  I suppose there is a smarter way to write the dax formula than below example with are hard-corded and could be high maintenance in the future. 

Once writing the calculated column above, create an inactive relationship between that calculated cash flow timing date column with your calendar table.

And then, write a userelationship dax formula to activate that inactive relationship of the cash flow timing date in the fact table. 

The result is as shown above in the last column which repects your quarterly timing of tax cash outflow in comparison to the usual dateadd solution which doesn't respect the quarterly lump sum payments timing.  

I attach an example pbix file which demonstrates this in a simple way.  

Best regards,