Forum Discussion

Tlotly's avatar
Tlotly
Icon for Helper V rankHelper V
1 year ago
Solved

3 months view in a matrix

Good day

 

Is it possible to only show last 3 months on the matrix, based on a Period selection? For example, if I select 202508, I need the matrix to show, Aug 2025, Jul 2025 and Jun 2025. 

 

 

The measure below calculates the combined last 3 months, based on the Period selection and it works perfectly on a card : 2312 card: Basically, the 2312 results should be shown per month on the matrix

TestCentresSubs =

VAR CurrentPeriod = MAX('Calendar'[Period]) --SELECTEDVALUE('CME'[gl_period]) // Get Current Period

VAR CurrentDate =
                    DATE(
                        LEFT(CurrentPeriod, 4),
                        RIGHT(CurrentPeriod, 2),
                        1
                    )
VAR StartDate = EOMONTH(CurrentDate, -3) + 1
VAR EndDate = EOMONTH(CurrentDate, 0)
VAR Last3Months =
                    FILTER(
                        ALL('Calendar'[Period], 'Calendar'[StMonth]),
                        'Calendar'[StMonth] >= StartDate &&
                        'Calendar'[StMonth] <= EndDate
                    )

-----VAR _submissions = CALCULATE(SUM(CME[ForCount]))----

RETURN

CALCULATE(
    SUMX(
        Last3Months,
        [NrofSubmissions]
    ),
    ALLSELECTED('Calendar'[Period])
)


 

 

 

 

Need Help
Tips and Tricks

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi Tlotly 

    You will need to use a disconnected table for the slicer and reference that in a measure.

     

    Replace the _XMonths value in the measure below.

    Total Revenue Last Six Months = 
VAR _XMonths = 6
VAR _EndDate =
    -- Get the latest selected date from the disconnected date slicer
    MAX ( DisconnectedDate[Date] )

VAR _StartDate =
    -- Calculate the start date by subtracting 6 months from the end date
    -- and adding 1 day to make it an inclusive range (e.g., from 1st to 30th)
    EDATE ( _EndDate, -_XMonths ) + 1

RETURN
    -- Calculate Total Revenue between the calculated start and end dates
    CALCULATE (
        [Total Revenue],
        -- Use KEEPFILTERS to retain any existing filters on Dates[Date]
        -- and apply the custom 6-month date range
        KEEPFILTERS ( Dates[Date] >= _StartDate && Dates[Date] <= _EndDate )
    )

     

    Please see the attached pbix.

  • Tlotly's avatar
    Tlotly
    Icon for Helper V rankHelper V
    1 year ago

    Thank you all for your solutions. They were very helpful, and everything is now working perfectly.