Forum Discussion

DLJ's avatar
DLJ
Frequent Visitor
6 years ago
Solved

Calculate Total Expenses Excluding Current Month

Hi All,

 

I want to show a bar chart comparing month by month 2 different measures:

a) Total actual expenses (from the origin to the last month extracted -but excluding the current month-)

b) Total expenses budgeted (from the origin to the end of the project; in this case we don't have to exclude any date)

 

My question would be what measure can I use for the Total Actual Expenses (a), excluding the current month inside the formula?

 

I think it has to be inside the formula. The problem is that I can't use a slicer as a filter for dates for the specific visual object, as I do want to show all dates for the comparative measure "Total expenses budgeted (b).

 

So I think that the only solution would be to create a measure with CALCULATE filtering dates in somewhow... E.g.:

CALCULATE ([Total actual expenses];ALLEXCEPT('DATES';¿(current month)?))

 

Another idea? I would be very grateful if someone could solve this question.

Thanks

Regards

  • Anonymous's avatar
    Anonymous
    6 years ago

    you could create a custom column like this:

     

    IsCurrentMonth = 
        IF (
            YEAR ( Table[Date] ) = YEAR ( TODAY () )
                && MONTH ( Table[Date] ) = MONTH ( TODAY () ),
            "Yes",
            "No"
        )

     

     

    and then filter the visual on this column -> IsCurrentMonth = "No" 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    you could create a custom column like this:

     

    IsCurrentMonth = 
        IF (
            YEAR ( Table[Date] ) = YEAR ( TODAY () )
                && MONTH ( Table[Date] ) = MONTH ( TODAY () ),
            "Yes",
            "No"
        )

     

     

    and then filter the visual on this column -> IsCurrentMonth = "No" 

    • DLJ's avatar
      DLJ
      Frequent Visitor

      Thank you very much. It works perfectly!