Filters for Current Year/Quarter/Month based on Year Selection

[JemmaD]

Hi there,

I have a calculation group with Current Year/Quarter/Month and Previous Year/Quarter/Month measures, so users can toggle a view of data. 

However i'm using YTD/QTD/MTD measures at the moment which accumulates the data, and I want it to not accumulate.

I also have a year filter in my report, so if a user select 2023 (last year) I want the 'Current Year' to point to 2023 and Previous Year to 2022.

So if 2024 is selected in the Year filter, the 'Current Year' would point to 2024 and Previous Year would be 2023.

And I guess for the quarters and month measures, it would point to which ever quarter or month we are currently in, based on the year selected so if 2023 is filtered and we are in Jan 2024 at the time, current quarter would be Q1 2023 and previous quarter would be Q1 2022.

I hope this makes sense, can you help me with syntax?

[saurabhtd · Accepted solution]

@JemmaD  You can use below DAX logics for calculation items in Calculation group 

Current Year = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear
        )
    )

Previous Year = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear - 1
        )
    )

Current Quarter = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR CurrentQuarter = QUARTER(TODAY())
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear
                && QUARTER('Date'[Date]) = CurrentQuarter
        )
    )

Previous Quarter = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR CurrentQuarter = QUARTER(TODAY())
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear - 1
                && QUARTER('Date'[Date]) = CurrentQuarter
        )
    )

Current Month = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR CurrentMonth = MONTH(TODAY())
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear
                && MONTH('Date'[Date]) = CurrentMonth
        )
    )

Previous Month = 
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR CurrentMonth = MONTH(TODAY())
RETURN
    CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
            ALL('Date'),
            'Date'[Year] = SelectedYear - 1
                && MONTH('Date'[Date]) = CurrentMonth
        )
    )

[JemmaD]

@saurabhtd you absolute legend! Thank you SO much this is perfect - and your naming convensions match mine so it was pretty easy to replicate. I really appreciate your help 😀

[saurabhtd]

@JemmaD You are welcome.