Forum Discussion

am_i_really's avatar
am_i_really
Frequent Visitor
3 years ago
Solved

SUM and filter by month

Hi All, 

 

I'm new to DAX and am having some trouble with this measure:

 

I need to SUM 3 columns from different tables, however when I apply a month filter to the page it does not filter the SUM value so I am looking for a solution to filter the measure by month. I have a date table and would like to filter by Month_Year.

 

Total Budget = CALCULATE(SUM('Catering Budget'[Catering Budget]) + SUM('Dining Subsidy'[Dining Budget]) + SUM('Office Coffee Budget'[Office Coffee Budget]))
 
Thanks in advance!


"DAX"
DAX Power BI
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi am_i_really ,

     

    I suggest you to create a calendar table and then create relationships between "Calendar" and these three tables.

    Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Month_Year", FORMAT ( [Date], "MMMM_YYYY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago

    Hello are you sure you made relationship between the tables?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi am_i_really ,

     

    I suggest you to create a calendar table and then create relationships between "Calendar" and these three tables.

    Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Month_Year", FORMAT ( [Date], "MMMM_YYYY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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