VAT Payment every July, October, January & April

[AndrejZitnay]

Hello all,

Would you be so kind and hlep me with measure for my VAT payments?

I have VAT as measure [VAT] and date from date table which is linked to my mesure via relationshop where I have dates as [MMM-YY"] but for calucation I use [MM/DD/YYYY]

I have always only one day in month and there are no gaps.

I know how to calculate cumulatives but I don't know how to calulacate sum for previews 3 months

Jan VAT  payment is = sum of Oct,Nov & Dec

Apr VAT paymet is = sum of Jan, Feb & March

July VAT payment is = sum of Apr, May & June

Oct VAT payment is = sum of Jul, Aug & Sep

etc for few years

Many thanks.

Andrej

[amitchandak · Accepted solution]

@AndrejZitnay , In you date table create a flag

flag = if(month([Date] in {1,4,7,10}, 1,0)

then try a measure like this and filter the page of visual on above flag

Rolling 3 = CALCULATE(sum(Table[Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-1) ,-3,MONTH))

[Anonymous]

@amitchandak 

Hi Amit, I have a doubt. Will a time intelligence function always work correctly and give accurate results under all scenarios in the absence of a calendar table or tables with missing dates in the date column? Especially so when it is not marked as a "Date Table".  @AndrejZitnay  said his table has only one date per month. In that scenario, I felt skeptical about using a time intelligence function.

Also, in this article at sqlbi by @marcorusso  https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/  , it was suggested to add an ALL(<datefield>) to the CALCULATE function in the absence of a date table.

What is the implication here? 

[AndrejZitnay]

Hello @amitchandak ,

Thank you. It does work. 

CF VAT Cashflow calc = CALCULATE([VAT Base for VAT Cashflow],DATESINPERIOD(PayMonthTable[PayMonth-cost],eomonth(MAX(PayMonthTable[PayMonth-cost]),-1) ,-3,MONTH))*SUMX(PayMonthTable,PayMonthTable[flag])

[Anonymous]

Hi,

There are many ways to handle this kind of calculation either by using the internal Date/Time intelligence functions or by way of writing your own Date/Time calculations in DAX. But in almost all the cases involving Date/Time intelligence calculations, the calculation relies on having a calendar table with all the dates in the year without any gaps and marking it as a date table. To write such measures, the awareness about the various relationships to the calendar table is also mandatory, otherwise, the formula will look correct, but the result will be wrong and unless you already know the result and able to cross-verify, there is no way a user will be able to know if the result is correct or not.

You mentioned that your table has only one date per month... (If I understood it correctly), therefore I am not suggesting any time intelligence functions or similar custom functions, instead, we will use only two functions RANK (to rank the dates in ascending order) and a MONTH (to determine the calendar month number) to arrive at the solution.

Given below is the solution:

Assuming there is a table named "VAT Data" with the following sample data.

Date	VAT
31-05-2021	2
30-06-2021	3
31-07-2021	4
31-08-2021	5
30-09-2021	6
31-10-2021	7
30-11-2021	8
31-12-2021	9
31-01-2022	10
28-02-2022	11
31-03-2022	12
30-04-2022	13
31-05-2022	14
30-06-2022	15
31-07-2022	16
31-08-2022	17
30-09-2022	18
31-10-2022	19
30-11-2022	20
31-12-2022	21
31-01-2023	22

Add four calculated columns to this table using the following expressions.

Date Rank =
RANKX ( 'VAT Data', 'VAT Data'[Date], 'VAT Data'[Date], ASC, DENSE )

VAT Payment From = 'VAT Data'[Date Rank]-3

VAT Payment To = 'VAT Data'[Date Rank]-1

MonthNumber = MONTH('VAT Data'[Date])

The table will now look like this...

Date	Monthly VAT	Date Rank	VAT Payment From	VAT Payment To	MonthNumber
31 May 2021	2	1	-2	0	5
30 June 2021	3	2	-1	1	6
31 July 2021	4	3	0	2	7
31 August 2021	5	4	1	3	8
30 September 2021	6	5	2	4	9
31 October 2021	7	6	3	5	10
30 November 2021	8	7	4	6	11
31 December 2021	9	8	5	7	12
31 January 2022	10	9	6	8	1
28 February 2022	11	10	7	9	2
31 March 2022	12	11	8	10	3
30 April 2022	13	12	9	11	4
31 May 2022	14	13	10	12	5
30 June 2022	15	14	11	13	6
31 July 2022	16	15	12	14	7
31 August 2022	17	16	13	15	8
30 September 2022	18	17	14	16	9
31 October 2022	19	18	15	17	10
30 November 2022	20	19	16	18	11
31 December 2022	21	20	17	19	12
31 January 2023	22	21	18	20	1

Now create a measure named  VAT Payment with the following code.

VAT Payment =
VAR CurrentMonth =
    SELECTEDVALUE ( 'VAT Data'[Date Rank] )
VAR FromMonth = CurrentMonth - 3
VAR ToMonth = CurrentMonth - 1
VAR Amount =
    SUMX (
        FILTER (
            ALL ( 'VAT Data' ),
            'VAT Data'[Date Rank] >= FromMonth
                && 'VAT Data'[Date Rank] <= ToMonth
        ),
        'VAT Data'[Monthly VAT]
    )
VAR CurrentMonthNumber =
    SELECTEDVALUE ( 'VAT Data'[MonthNumber] )
VAR ReturnValue =
    IF ( CurrentMonthNumber IN { 1, 4, 7, 10 }, Amount, BLANK () )
RETURN
    ReturnValue

 This will give the following result.

Actually, the DAX code mentioned here uses a very rudimentary approach without using any of the inbuilt date/time intelligence functions. I have not worried about the best or efficient method, and just used what suits this scenario where there is no standard date table available and only one date is there for every month. 

But if you could change to a standard model with a proper date table, you can rewrite the entire thing using a combination of functions like SUMX, EDATE, STARTOFTHEMONTH, ENDOFTHEMONTH, DATESBETWEEN, SELECTEDVALUE etc...

It is up to you whether to change the data model for the sake of using such functions or just use the functions that fit the current model. 

[AndrejZitnay]

Hello @Anonymous ,

Many thanks for your help.

I've tried to follow your guiadance but I have no otucome. 

(There is no error in formula just result is blank)

Did I missed anything?

My VAT Base comes from this measrue 

VAT Base for VAT Cashflow = CALCULATE(SUMX(PayMonthTable,[CF Input VAT (131CF)]+[CF Income Vat (93CF)]))

CF VAT Cashflow calc = VAR CurrentMonth =

SELECTEDVALUE ( 'PayMonthTable'[PayMonth-cost] )

VAR FromMonth = CurrentMonth - 3

VAR ToMonth = CurrentMonth - 1

VAR Amount =

SUMX (

FILTER (

ALL ( PayMonthTable ),

PayMonthTable[VAT Payment From] >= FromMonth

&& PayMonthTable[VAT Payment To] <= ToMonth

),

[VAT Base for VAT Cashflow]

)

VAR CurrentMonthNumber =

SELECTEDVALUE ( PayMonthTable[MonthNumberNew] )

VAR ReturnValue =

IF ( CurrentMonthNumber IN { 1, 4, 7, 10 }, Amount, BLANK () )

RETURN

ReturnValue