YTD and Last month in one table based on selection year and month

[Anonymous]

Hello All,

I have two slicers:

- Month selection

- Year selection

I want to make two columns in a table:

- SUM of last month

- SUM of YTD

Both based on the selection, so based on the maximum selected value (Year and Month), even when people select more than one value. The fact table is directly related to the calendar table.

I tried a lot, but I think/hope I miss the simple trick.

Thanks in advance!

Halmar

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

SUM of last month = 
VAR __YEAR =
    MAX ( 'Calendar'[Year] )
VAR __MONTH =
    MAX ( 'Calendar'[Month Number] )
VAR __MIN_DATE =
    EDATE ( DATE ( __YEAR, __MONTH, 1 ), -1 )
VAR __MAX_DATE =
    DATE ( __YEAR, __MONTH, 1 )
VAR __RESULT =
    CALCULATE (
        [Measure],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= __MIN_DATE
                && 'Calendar'[Date] < __MAX_DATE
        )
    )
RETURN
    __RESULT

SUM of YTD = 
VAR __YEAR =
    MAX ( 'Calendar'[Year] )
VAR __MONTH =
    MAX ( 'Calendar'[Month Number] )
VAR __RESULT =
    CALCULATE (
        [Measure],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = __YEAR
                && 'Calendar'[Month Number] <= __MONTH
        )
    )
RETURN
    __RESULT

result:

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data