Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DLJ
Frequent Visitor

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

1 ACCEPTED SOLUTION
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" 

View solution in original post

2 REPLIES 2
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
Frequent Visitor

Thank you very much. It works perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.