Forum Discussion

NatK's avatar
NatK
Icon for Helper I rankHelper I
5 years ago
Solved

To-Date Calculation with multiple filters

In the code below, I am trying to calculate the all time balance as of selected date with one exclusion: If the date is the last day of the selected year (12/31/YYYY) and [Closing Entry] = 1, exclude the amount from the total balance. Closing Entry is a 1 or 0.

 

For example, I have a slicer for December 2020. For entries with the date of 12/31/2020, exclude rows in which Closing Entry = 1. 

 

When trying out this measure, the values for 12/31/2020 with Closing Entry = 1 appears to be skipped. However, the total is not showing. Any idea what is missing here?

 

Balance to Date =

VAR lastDayofMonth = LASTDATE(datetable[Date])

VAR lastDayofYear = ENDOFYEAR(datetable[Date])

VAR checkEOY = IF(lastDayofMonth=lastDayofYear, TRUE(), FALSE())

VAR checkClosedEntry = IF(max(facttable[Closing Entry])=1, TRUE(), FALSE())

VAR balance = CALCULATE([Amount], datetable[Date]<=lastDayofMonth

&&  (checkEOY=TRUE && checkClosedEntry=FALSE) 

)

RETURN balance

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi NatK ,

     

    Please try this:

    Balance to Date =
VAR lastDayofMonth =
    LASTDATE ( 'datetable'[Date] )
VAR checkClosedEntry =
    IF ( MAX ( 'facttable'[Closing Entry] ) = 1, TRUE (), FALSE () )
VAR balance =
    CALCULATE (
        SUM ( facttable[Amount] ),
        FILTER (
            'facttable',
            'facttable'[Date] <= lastDayofMonth
                && checkClosedEntry = FALSE
        )
    )
RETURN
    balance

    Then use SUMX() function:

    Measure = SUMX('facttable',[Balance to Date])

    The final output is shown below:

    Here is the pbix file.


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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    NatK , Still not very sure complete logic is correct.

     

    Try to change balance , like this and try

    VAR balance = CALCULATE([Amount], datesmtd(datetable[Date]), filter(facttable, checkEOY=TRUE && checkClosedEntry=FALSE))

    • NatK's avatar
      NatK
      Icon for Helper I rankHelper I
      5 years ago

      amitchandak, thanks for your reply. There are two requirements I still need:

       

      1) The balance needs to be inception to date. There is a month slicer, but when a month is selected the balance needs to capture the balance from inception (2019 in my case) through selected month.

      2) The total is still blank and does not appear in my visuals

       

      Below is an example of what I am seeing. December 2020 is selected. Note that balance is omitting 12/31/2020 where Closing Entry = 1, which is expected. However, the total is blank.

       

      • NatK's avatar
        NatK
        Icon for Helper I rankHelper I
        5 years ago

        I also want to add that if the Closing Entry is all 0's, the balance total will appear. 


        Example: 

  • NatK's avatar
    NatK
    Icon for Helper I rankHelper I
    5 years ago

    Hi amitchandak, I was able to get a little further with this. I am now getting a total for balance, but the total is incorrect. In the screenshot below, the total of -$320 is ignoring the last row.

     

    Here is my updated code:

    balance = 
    VAR lastDayofSelectedPeriod = LASTDATE(datetable[Date])
    VAR checkClosedEntry = IF(Max(testdata[Closing Entry])=1, TRUE(), FALSE())
    VAR new= CALCULATE(SUM(testdata[Amount]), datetable[Date] < lastDayofSelectedPeriod || (datetable[Date]=lastDayofSelectedPeriod && checkClosedEntry=false)) //Amount after OR is not getting calculated
    return new
     
    Any ideas what may be causing the incorrect total? Thank you in advance.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi NatK ,

     

    Please try this:

    Balance to Date =
VAR lastDayofMonth =
    LASTDATE ( 'datetable'[Date] )
VAR checkClosedEntry =
    IF ( MAX ( 'facttable'[Closing Entry] ) = 1, TRUE (), FALSE () )
VAR balance =
    CALCULATE (
        SUM ( facttable[Amount] ),
        FILTER (
            'facttable',
            'facttable'[Date] <= lastDayofMonth
                && checkClosedEntry = FALSE
        )
    )
RETURN
    balance

    Then use SUMX() function:

    Measure = SUMX('facttable',[Balance to Date])

    The final output is shown below:

    Here is the pbix file.


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

    • NatK's avatar
      NatK
      Icon for Helper I rankHelper I
      5 years ago

      Hi Anonymous,

       

      Thank you for your help and apologies for mydelayed response. There was one requirement in your solution that was not met: the balance to date must include entries with Closing Entry = 1 from prior years. If we are slicing for December 2020, we would ignore Closing Entry=1 for 2020, but include Closing Entry = 1 for 2019 and prior years (I'm aware my example data did not have closing entry for prior years which may have caused confusion). How would you update your code to include closing entries for prior years?

       

      Thanks again,

      Natalie