Forum Discussion

rbowen's avatar
rbowen
Helper III
1 year ago

Help With VAR Function, Or Should I Use A Different Approach?

I'm creating a basic table showing both YTD and MTD trial balance data for our current fiscal year. For now, I'm just focusing on the first 4 months of the FY. The YTD and MTD names are somewhat misleading because I'm not using a date table nor is there any date related information in the table I pull the raw data from other than the month name. Here's how I'm doing this so far:

 

The YTD value is created by first creating two calculated columns, one for debits, another for credits. I then create a third calculated column that calculates the difference between the debits and credits columns. This is my NetChange column. The final step in creating the YTD values is a measure that just sums the NetChange column:

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

 

Then, I get each month's YTD value by creating 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
 
Putting the YTD measure in my table, I get the correct YTD values for each month

 

 

The trouble starts when I put the MTD values in the table. MTD values must be calculated as follows:

 

September, first month of the FY, will always equal the September YTD value.

OctoberMTD = OctoberYTD - SeptemberYTD  

NovemberMTD = NovemberYTD - OctoberYTD  

DecemberMTD = DecemberYTD - NovemberYTD  

 

My MTD measure is:

 

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 are what the MTD values should be:
 

 

And here's how they come out in BI. September's is working but the rest are not. I've used the VAR function successfully in the past for similar types of operations, but it's not working now for some reason. I've got some piece of the syntax wrong obviously but what am I missing?

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, rbowen 

    Update your measures as follows:

    SeptSum = CALCULATE(SUM(AcctBal[NetChange]), FILTER(ALLSELECTED('AcctBal'[Month]), 'AcctBal'[Month] = "September"))

    I added in every xxSum:

    ALLSELECTED('AcctBal'[Month])

    Here are the results:

     

    Best Regards

    Jianpeng Li

    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
      1 year ago

      Thank you Jianpeng Li, I was really hoping that would be the solution because of its simplicity. Unfortunately, it didn't change the MTD numbers at all and they're still the same as the YTD numbers. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, rbowen 

        Thank you very much for your feedback. Try this expression:

        OctSum = CALCULATE(SUM(AcctBal[NetChange]), FILTER(ALLSELECTED('AcctBal'), 'AcctBal'[Month] = "October"))

        Make sure you are not affected by the row context. I've provided the PBIX file used this time below.

         

        Best Regards

        Jianpeng Li

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

         

         

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    1 year ago

    Hi rbowen,

     

    I strongly suggest changing your approach to one with a date table and a date in whatever table you're using now, instead of just a month name. That way, you can use date logic for your calculations instead of hardcoding a bunch of unintuitive logic.

     

    It should be simple enough to turn your month into a date, especially if you have a year column too. A calculated column like the below should work:

    Trial Balance Date = DATEVALUE ( Table1[Month] & " " & Table1[Year] )


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.