Help With DAX VAR Function

[rbowen]

I'm creating a basic table in BI Desktop showing both YTD and MTD trial balance data for our current fiscal year. The examples in this post just focus on the first 4 months for now - if I can get those correct, the rest should be easily calculated. The YTD and MTD designations are somewhat misleading as there is no date information in the DB table I pull this data from and I'm not using the standar MTD/YTD DAX and there's no date table. Instead, here's how the process works:

The YTD is created by first creating two calculated columns, one for debits, another for credits. I then create a third calculated that calculates the difference between debits and credits, this is my NetChange column. To get YTD values, I create a measure that sums the NetChange column.

YTD = CALCULATE(SUM(AcctBal[NetChange]))

The YTD values are coming out correctly for the months shown below. The problem is creating the MTD values for each month. MTD values must be calculated as follows:

September, the first month of the fiscal year, will always equal September's YTD value - so far so good.
October = OctoberYTD - SeptemberYTD
November = NovemberYTD - OctoberYTD
December = DecemberYTD - NovemberYTD

In the past, I've used VAR functions to successfully accomplish things similar to this in the past but it's not working for some reason now. For each of the months shown, I created a measure to sum each month's YTD value:

SeptSum = CALCULATE(SUM(AcctBal[NetChange]),FILTER('AcctBal','AcctBal'[Month] = "September"))
OctSum = CALCULATE(SUM(AcctBal[NetChange]),FILTER('AcctBal','AcctBal'[Month] = "October"))
and so on thru December

For the MTD values, I created the following measure:

MTD =
VAR Month = [SelectMonth]
VAR MTDSep = [SeptSum]
VAR MTDOct = [OctSum]-[SeptSum]
VAR MTDNov = [NovSum]-[OctSum]
VAR MTDDec = [DecSum]-[NovSum]
Return
IF (Month="September",MTDSep,
IF (Month="October",MTDOct,
IF (Month="November",MTDNov,
IF (Month="December",MTDDec))))

Here's what the MTD values should be:

MainAcct	SubAcct	Month	YTD	MTD
40310	1002	Septemer	($297.00)	-$297.00
40310	1002	October	($170.48)	$126.52
40310	1002	November	($523.47)	-$352.99
40310	1002	December	($2,043.43)	-$1,519.96

Here's what they're coming out as with my current MTD measure. September's MTD is correct but the other three are just matching their YTD counterparts. Am I missing something in the MTD measure, or am I overthinking this and it could be accomplished with something simpler?

'm creating a basic table in BI Desktop showing both YTD and MTD trial balance data for our current fiscal year. The examples in this post just focus on the first 4 months for now - if I can get those correct, the rest should be easily calculated. The YTD and MTD designations are somewhat misleading as there is no date information in the DB table I pull this data from and I'm not using the standar MTD/YTD DAX and there's no date table. Instead, here's how the process works:

The YTD is created by first creating two calculated columns, one for debits, another for credits. I then create a third calculated that calculates the difference between debits and credits, this is my NetChange column. To get YTD values, I create a measure that sums the NetChange column.

YTD = CALCULATE(SUM(AcctBal[NetChange]))

The YTD values are coming out correctly for the months shown below. The problem is creating the MTD values for each month. MTD values must be calculated as follows:

September, the first month of the fiscal year, will always equal September's YTD value - so far so good.
October = OctoberYTD - SeptemberYTD
November = NovemberYTD - OctoberYTD
December = DecemberYTD - NovemberYTD

In the past, I've used VAR functions to successfully accomplish things similar to this in the past but it's not working for some reason now. For each of the months shown, I created a measure to sum each month's YTD value:

SeptSum = CALCULATE(SUM(AcctBal[NetChange]),FILTER('AcctBal','AcctBal'[Month] = "September"))
OctSum = CALCULATE(SUM(AcctBal[NetChange]),FILTER('AcctBal','AcctBal'[Month] = "October"))
and so on thru December

For the MTD values, I created the following measure:

MTD =
VAR Month = [SelectMonth]
VAR MTDSep = [SeptSum]
VAR MTDOct = [OctSum]-[SeptSum]
VAR MTDNov = [NovSum]-[OctSum]
VAR MTDDec = [DecSum]-[NovSum]
Return
IF (Month="September",MTDSep,
IF (Month="October",MTDOct,
IF (Month="November",MTDNov,
IF (Month="December",MTDDec))))

Here's what the MTD values should be:

MainAcct	SubAcct	Month	YTD	MTD
40310	1002	Septemer	($297.00)	-$297.00
40310	1002	October	($170.48)	$126.52
40310	1002	November	($523.47)	-$352.99
40310	1002	December	($2,043.43)	-$1,519.96

Here's what they're coming out as with my current MTD measure. September's MTD is correct but the other three are just matching their YTD counterparts. Am I missing something in the MTD measure, or am I overthinking this and it could be accomplished with something simpler?

[johnt75]

The simplest way I think would be to create a column in either Power Query or DAX to turn the month name into an actual date representing the first of that month. Then link a date table to that and you can either use the standard time intelligence functions or write your own.

[Anonymous · Accepted solution]

Hi @rbowen ,

Based on your description, you are having a problem calculating the MTD, right?
Since you don't have a date column in your datasheet, we recommend that you create a new index column to align the month column, which will guide you in calculating the MTD.

MTD = 
VAR CurrentMonthYTD = CALCULATE(
    SUM(AcctBal[NetChange]),
    FILTER(AcctBal, AcctBal[Month] = MAX(AcctBal[Month]))
)
VAR PreviousMonthYTD = CALCULATE(
    SUM(AcctBal[NetChange]),
    FILTER(
        ALL(AcctBal),
        AcctBal[MonthIndex] = MAX(AcctBal[MonthIndex]) - 1
    )
)
RETURN
IF (
    SELECTEDVALUE(AcctBal[MonthIndex]) = 1,
    CurrentMonthYTD,
    CurrentMonthYTD-PreviousMonthYTD
)

If you have any other questions, you can check out my pbix file and I would be honored to help you out!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rbowen]

Hi Xingshen and thank you for the suggestion. This seems to be working great and even simplifies the process so that I don't have to write multiple measures for each month.  Many thanks.