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

The Fabric Community site will be in read-only mode on Monday, Feb 24 from 12:01 AM to 8 AM PST for scheduled upgrades.

Reply
Sonita
Frequent Visitor

Combine selected value (slicer) with sum for selected period and previous period

Hi,
I´m wondering if is possible to combine in one matrix these calculated values.
I need something like this:

Sonita_0-1723791104518.png

I will be OK with this format too:

Sonita_1-1723791150684.png

 

visual is affected with this slicer:

Sonita_2-1723791180521.png


I try to use two measure in one matrix.

The first one is:

Sum Annualized Booking Value (Selected Year Marker) = 
VAR SelectedYear = 
    SWITCH(
        SELECTEDVALUE(general_date_table[Year Marker]),
        "YTD Marker", YEAR(TODAY()),
        "1 Year Ago", YEAR(TODAY()) - 1,
        "2 Years Ago", YEAR(TODAY()) - 2,
        "3 Years Ago", YEAR(TODAY()) - 3,
        BLANK()
    )

RETURN
CALCULATE(
    [Sum Total Contract Value],
    USERELATIONSHIP(contract_item_history[contract_revision_approved_date], general_date_table[Date]),
    YEAR(contract_item_history[contract_revision_approved_date]) = SelectedYear
)

 


works perfectly

 

the secont one, doesn´t work with the slicer:

_test_Sum Annualized TCV = 
VAR year_ago = CALCULATE(
    [Sum Total Contract Value],
    FILTER(
        contract_item_history,
        YEAR(contract_item_history[contract_revision_approved_date]) = YEAR(TODAY()) - 1
    )
)
VAR two_years_ago = CALCULATE(
    [Sum Total Contract Value],
    FILTER(
        contract_item_history,
        YEAR(contract_item_history[contract_revision_approved_date]) = YEAR(TODAY()) - 2
    )
)

VAR three_years_ago  = CALCULATE(
    [Sum Total Contract Value],
    FILTER(
        contract_item_history,
        YEAR(contract_item_history[contract_revision_approved_date]) = YEAR(TODAY()) - 3
    )
)

VAR four_years_ago  = CALCULATE(
    [Sum Total Contract Value],
    FILTER(
        contract_item_history,
        YEAR(contract_item_history[contract_revision_approved_date]) = YEAR(TODAY()) - 4
    )
)

VAR _selected_year = SELECTEDVALUE(general_date_table[Year Marker])

RETURN
SWITCH(
    TRUE(),
    _selected_year = "YTD Marker", year_ago,
    _selected_year = "1 Year Ago", two_years_ago,
    _selected_year = "2 Years Ago", three_years_ago,
    _selected_year = "3 Years Ago", four_years_ago,
    "N/A"
)

 


I tried a simple one too, but doesn´t work neither:

_test Sum for Previous Year =
CALCULATE(
    [Sum Total Contract Value],
    FILTER(
        contract_item_history,
        YEAR(contract_item_history[contract_revision_approved_date]) =
            SWITCH(
                SELECTEDVALUE(general_date_table[Year Marker]),
                "YTD Marker", YEAR(TODAY()) - 1,
                "1 Year Ago", YEAR(TODAY()) - 2,
                "2 Years Ago", YEAR(TODAY()) - 3,
                "3 Years Ago", YEAR(TODAY()) - 4,
                BLANK()
            )
    )
)<p><br />Any idea why is not working and how to fix it?<br /><br /></p><p> </p><p>Thanks a lot in advanced<br />Sona</p>

 

1 REPLY 1
lbendlin
Super User
Super User

As soon as you are trying to render different formats (percentages and numbers) you need to either "show values on rows"  or have all your measures return text.

 

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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