Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
@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))
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?
Hello @amitchandak ,
Thank you. It does work.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |