Sum of value based on other months

[rorand]

Hello!
I hope someone can help me with this. Unfortunately I cannot share my data as it is sensitive, but I will try to explain as good as I can.

I have a few tables, or a lot of them actually, but I hope to solve this without getting to much into relations.
One of the tables is G/L entries. This table is entries by date and some other dimensions.

From this I have summed how much VAT our business invoice our customers.

Then I have made a forecast for the invoiced VAT (e.g. sum previous year * 6,3%) .

The next thing I need to to is periodize this forecast to the months that our business pay the VAT.
VAT from months 1 & 2 is payed in month 4
VAT from months 3 & 4 is payed in month 6
VAT from months 5 & 6 is payed in month 8

VAT from months 7 & 8 is payed in month 10

VAT from months 9 & 10 is payed in month 12

VAT from months 11 & 12 is payed in month 2 the next year

I have a calendar table, and when I use month as columns, I want the paying months (2,4,6,8,10,12) to show the sum of the related forecast months.

I need to use the standard month (and not a "VAT pay-month"), because I want to use this data in a matrix with other data. Because of this I am thinking it would be best to solve this as a calculated column, but I am not sure at all.

My DAX is just not good enough to solve this, also - as you can see - I am not sure if this would be best to do as a calculated column or a measure.

Regards

Andreas

[Anonymous · Accepted solution]

You'll notice that the differences are in months 2 and 4 only. That most likely means my calculation is correct because DAX always does the same thing (moving the latest 2 months back in time by 2 months), and hence it's highly unlikely that it's the DAX that is the culprit.

You should check what boundaries the DATEADD/DATESINPERIOD function returns and if they tally with what you think it should return. It may be that the function returns correct boundaries (according to the time-intel algorithm, which not always is intuitive) and you want something else. But if that's the case, you'll have to write your own time calculation in place of DATEADD/DATESINPERIOD.

Here's your testing version; read it well and use accordingly.

// YearMonth must be a hidden field
// in the Date table equal to the
// int number YYYYMM. MonthNumberInYear
// is the standard number of the month
// in year, 1,2,3,...,12.

[VAT Paid] =
var __oneFullMonthVisible =
	if(
		HASONEVALUE( Date[YearMonth] ),
		
		var __visibleDayCount = COUNTROWS( 'Date' )
		var __daysInMonth =
			CALCULATE(
				COUNTROWS( 'Date' ),
				VALUES( Date[YearMonth] ),
				ALL( 'Date' )
			)
		return
			( __visibleDayCount = __daysInMonth ),
			
		FALSE()	
	)
var __evenMonth =
	ISEVEN( SELECTEDVALUE( Date[MonthNumberInYear] ) )
var __result =
	if( __oneFullMonthVisible && __evenMonth,
	
		var __lastDayVisible = LASTDATE( Date[Date] )
		var __periodToCalcOver =
			DATEADD(
				DATESINPERIOD(
					Date[Date],
					__lastDayVisible,
					-2,
					Month
				),
				-2,
				Month
			)
		// 2 vars for testing only
		// remove when done
		var __startPeriod =
			MINX(
				__periodToCalcOver,
				Date[Date]
			)
		var __endPeriod = 
			MAXX(
				__periodToCalcOver,
				Date[Date]
			)
		// Remove the 2 above when
		// done testing.
		var __vatPaid =
			CALCULATE(
				[VAT Invoiced],
				__periodToCalcOver
			)
		return
			// __vatPaid
			// Remove the two lines belowe
			// after you're done testing.
			// __endPeriod
			__startPeriod
	)
return
	__result

Best
D

[mahoneypat]

Not totally clear on your model/scenario, but would it be solved simply by adding a column to your Date table to indicate if that month is a VAT month?  If so, you could use a simple formula like 

VAT Month = ISEVEN(MONTH('Date'[Date]))

Then you could write a measure that checks if this column is True or False (or you could make an If to give more descriptive terms) to return a value or blank.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[rorand]

Hi @mahoneypat and thank you for your reply.

I've tried something similar, mainly by making a new column with the payment month for the VAT.

But my main problem is to find out how to calculate the DAX function to periodize the VAT from the Invoicing month to the payment month.
If this is my SUM VAT by month in column SUM VAT INVOICED, I am trying to find the funtion to calculate SUM VAT PAID:

Year_Month	SUM VAT INVOICED	SUM VAT PAID	Explanation
2019_01	1000
2019_02	1500
2019_03	2000
2019_04	2500	2500	Paid = sum of month 1 & 2 in invoiced
2019_05	1500
2019_06	2000	4500	Paid = sum of month 3 & 4 in invoiced
2019_07	5000
2019_08	6000	3500	Paid = sum of month 5 & 6 in invoiced
2019_09	1000
2019_10	6000	11000	Paid = sum of month 7 & 8 in invoiced
2019_11	1000
2019_12	1000	7000	Paid = sum of month 9 & 10 in invoiced
2020_01
2020_02		2000	Paid = sum of month 11 & 12 in invoiced

I tried IF a couple of times, this almost worked:

VAT_Periodized = 
IF([Month] = 2;
    CALCULATE(
        [VAT];
        DATESINPERIOD( 'Calendar'[Date];
        EOMONTH( 'Calendar'[Date]; -3 );
        2; MONTH)
    );
IF([Month] = 4;
    CALCULATE(
        [VAT];
        DATESINPERIOD( 'Calendar'[Date];
        EOMONTH( 'Calendar'[Date]; -3 );
        2; MONTH)
    );
And so on for months 6, 8, 10, 12.

Regards

Andreas

[Anonymous]

Here's your measure:

// YearMonth must be a hidden field
// in the Date table equal to the
// int number YYYYMM. MonthNumberInYear
// is the standard number of the month
// in year, 1,2,3,...,12.

[VAT Paid] =
var __oneFullMonthVisible =
	if(
		HASONEVALUE( Date[YearMonth] ),
		
		var __visibleDayCount = COUNTROWS( 'Date' )
		var __daysInMonth =
			CALCULATE(
				COUNTROWS( 'Date' ),
				VALUES( Date[YearMonth] ),
				ALL( 'Date' )
			)
		return
			( __visibleDayCount = __daysInMonth ),
			
		FALSE()	
	)
var __evenMonth =
	ISEVEN( SELECTEDVALUE( Date[MonthNumberInYear] ) )
var __result =
	if( __oneFullMonthVisible && __evenMonth,
	
		var __lastDayVisible = LASTDATE( Date[Date] )
		var __vatPaid =
			CALCULATE(
				[VAT Invoiced],
				DATEADD(
					DATESINPERIOD(
						Date[Date],
						__lastDayVisible,
						-2,
						Month
					),
					-2,
					Month
				)
			)
		return
			__vatPaid
	)
return
	__result

Best

D

[rorand]

Wow @Anonymous !
This works pretty good!!!

Are there any reason why some months calculate correct and others wrong, or do I have to search my data for issues?

This is my output, what it should be and the difference:

Year	Month	VAT	VAT Paid	VAT Paid should be	Diff
2018	11	25 990
2018	12	22 680
2019	1	19 839
2019	2	25 730	57 643	48 670	-8 973
2019	3	26 235
2019	4	26 619	68 248	45 569	-22 680
2019	5	29 965
2019	6	32 780	52 854	52 854	-
2019	7	38 887
2019	8	28 110	62 745	62 745	-
2019	9	24 764
2019	10	29 904	66 997	66 997	-
2019	11	30 623
2019	12	17 971	54 669	54 669	-
2020	1	19 563
2020	2	23 712	60 527	48 594	-11 933
2020	3	23 248
2020	4	29 470	61 246	43 275	-17 971
2020	5
2020	6		52 718	52 718	-

Regards

Andreas

[Anonymous · Accepted solution]

You'll notice that the differences are in months 2 and 4 only. That most likely means my calculation is correct because DAX always does the same thing (moving the latest 2 months back in time by 2 months), and hence it's highly unlikely that it's the DAX that is the culprit.

You should check what boundaries the DATEADD/DATESINPERIOD function returns and if they tally with what you think it should return. It may be that the function returns correct boundaries (according to the time-intel algorithm, which not always is intuitive) and you want something else. But if that's the case, you'll have to write your own time calculation in place of DATEADD/DATESINPERIOD.

Here's your testing version; read it well and use accordingly.

// YearMonth must be a hidden field
// in the Date table equal to the
// int number YYYYMM. MonthNumberInYear
// is the standard number of the month
// in year, 1,2,3,...,12.

[VAT Paid] =
var __oneFullMonthVisible =
	if(
		HASONEVALUE( Date[YearMonth] ),
		
		var __visibleDayCount = COUNTROWS( 'Date' )
		var __daysInMonth =
			CALCULATE(
				COUNTROWS( 'Date' ),
				VALUES( Date[YearMonth] ),
				ALL( 'Date' )
			)
		return
			( __visibleDayCount = __daysInMonth ),
			
		FALSE()	
	)
var __evenMonth =
	ISEVEN( SELECTEDVALUE( Date[MonthNumberInYear] ) )
var __result =
	if( __oneFullMonthVisible && __evenMonth,
	
		var __lastDayVisible = LASTDATE( Date[Date] )
		var __periodToCalcOver =
			DATEADD(
				DATESINPERIOD(
					Date[Date],
					__lastDayVisible,
					-2,
					Month
				),
				-2,
				Month
			)
		// 2 vars for testing only
		// remove when done
		var __startPeriod =
			MINX(
				__periodToCalcOver,
				Date[Date]
			)
		var __endPeriod = 
			MAXX(
				__periodToCalcOver,
				Date[Date]
			)
		// Remove the 2 above when
		// done testing.
		var __vatPaid =
			CALCULATE(
				[VAT Invoiced],
				__periodToCalcOver
			)
		return
			// __vatPaid
			// Remove the two lines belowe
			// after you're done testing.
			// __endPeriod
			__startPeriod
	)
return
	__result

Best
D

[rorand]

@Anonymous 🙌

With the minor changes you made it now works like a charm, thank you very much!

One last question, the totals per year is not showing with this measure - is there a way?

Regards

Andreas

[Anonymous · Accepted solution]

The totals are not showing because there is a condition built-in that checks for the number of full months visible in the current context. If only one month is visible, then the number is calculated. Otherwise, it's not. If you want to have the year total, you have many options. One of them is to replace the measure with another one:

// YearMonth must be a hidden field
// in the Date table equal to the
// int number YYYYMM. MonthNumberInYear
// is the standard number of the month
// in year, 1,2,3,...,12.

[VAT Paid] =

var __onlyFullMonthsVisible =
	var __visibleDayCount = COUNTROWS( 'Date' )
	var __daysInMonths =
		CALCULATE(
			COUNTROWS( 'Date' ),
			VALUES( Date[YearMonth] ),
			ALL( 'Date' )
		)
	return
		( __visibleDayCount = __daysInMonth )
		
var __result =
	if( __onlyFullMonthsVisible,
		
		SUMX(
			CALCULATETABLE(
				VALUES( Date[YearMonth] ),
				KEEPFILTERS( 
					ISEVEN( Date[MonthNumberInYear] )
				)
			),
			CALCULATE(
				var __lastDayVisible = LASTDATE( Date[Date] )
				var __periodToCalcOver =
					DATEADD(
						DATESINPERIOD(
							Date[Date],
							__lastDayVisible,
							-2,
							Month
						),
						-2,
						Month
					)
				var __vatPaid =
					CALCULATE(
						[VAT Invoiced],
						__periodToCalcOver
					)
				return
					__vatPaid
			)
		)
	)
	
return
	__result

Best
D

[rorand]

Thank you very much @Anonymous, the measure is now excactly as I want it to be!
I've struggled a lot with this the last couple of weeks, you just saved me a lot of time 🙌

Best regards

Andreas

[mahoneypat]

Ok.  I think I figured out one way to do it. 

1. Add a VAT Year_Month column with a query like this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLPDsIgDMZfpdl5h7bjz3bwHbzPxRinkcO2g87nF2SIWWgCadqPr79A6fuKkbozUlVXhIg+wHcPdZI4SLooNT7nsksFKbq2cLy4EQ7wXCdY7jAt8+sBBKcVkQ0wuNmv9+Kut/GvjZbhJsOVTGgSQQkE6426fIfW5yZKjUzQiWAEQie+LWEm0HakhLAJ0ZYRJI+POEtWJnSJQFhAMMYfArv+oc5bjeXm9Bsz7eY8fAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year_Month = _t, #"SUM VAT INVOICED" = _t, #"SUM VAT PAID" = _t, Explanation = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year_Month", type text}, {"SUM VAT INVOICED", Int64.Type}, {"SUM VAT PAID", Int64.Type}, {"Explanation", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Year_Month", "Year_Month - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Year_Month - Copy", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Year_Month - Copy.1", "Year_Month - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year_Month - Copy.1", Int64.Type}, {"Year_Month - Copy.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Year_Month - Copy.1", "Year"}, {"Year_Month - Copy.2", "Month"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each #date([Year], [Month],1)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "VAT Date", each Date.AddMonths([Date], 2+Number.Mod([Month],2))),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"VAT Date", type date}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type3", "VAT_YearMonth", each Text.From(Date.Year([VAT Date]))&"_"&Text.PadStart(Text.From(Date.Month([VAT Date])),2,"0")),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"VAT_YearMonth", type text}})
in
#"Changed Type4"

2.  Add a measure like this

VAT Sum = Sum(VAT[SUM VAT INVOICED)

However I suspect you are going to use your Date table for visuals, but DAX can be written to take advantage of the new column.  Let me know if you need help with that, now that I have it started.

3.  Add a table visual with the new VAT_YearMonth column and the new measure to get this

Not sure what you final visuals are but I think making this column in your query is key to it.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[rorand]

@mahoneypat 

---

@mahoneypat wrote:

However I suspect you are going to use your Date table for visuals, but DAX can be written to take advantage of the new column.  Let me know if you need help with that, now that I have it started.

☝️This is what I need help with 🙂
I also have gotten to the point where I have a VAT_Payment_Month column. But as I need to show other values - that is not periodizes in the same way as the VAT - in the same visuals as the VAT_Periodized, I cannot use the new month column for my visuals, because then the other values will be in the wrong month. So I have to use my date-table for my visuals.

Thanks a lot Pat! 🙂

Regards

Andreas

[mahoneypat]

If someone else doesn't solve it first today, I will work on it this evening.  Can you post an example table or chart of your desired output (e.g., which date column(s) are you using and an example of one of your other measures)?

Regards,

Pat