Forum Discussion

rbowen's avatar
rbowen
Helper III
1 year ago
Solved

Help With DAX VAR Function

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:

 

MainAcctSubAcctMonthYTDMTD
403101002Septemer($297.00)-$297.00
403101002October($170.48)$126.52
403101002November($523.47)-$352.99
403101002December($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:

 

MainAcctSubAcctMonthYTDMTD
403101002Septemer($297.00)-$297.00
403101002October($170.48)$126.52
403101002November($523.47)-$352.99
403101002December($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?

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      rbowen
      Helper III

      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. 

  • 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.