Trying to use SELECTEDVALUE filter in a MOM measure

[ewhaley]

I have set up 2 measures to return the numeric difference between this month and last month as well as the % difference. This works well when the table isn't filtered by anything else, so the values are the same for ALL records with the current month.

However, I want to add an additional filter on another column that should be set by the user's selection of a single value. I am not understanding how to set the alternate value when a single value is not selected. 

I also changed up the formula based on what I found when searching for how to do this. I was trying to replicate the #1 in #2 while being able to add an additional filter (Local Name). 

MEASURE VARIATION #1 > WORKING

SUM BU Size = SUMX(FILTER('Bargaining Unit Size','Bargaining Unit Size'[Report Month]=EARLIER('Bargaining Unit Size'[Report Month])),'Bargaining Unit Size'[Bargaining Unit Size])

MEASURE VARIATION #2 > NOT WORKING

SUM BU Size = CALCULATE(

    SUM('Bargaining Unit Size'[Bargaining Unit Size]),

    FILTER('Bargaining Unit Size', 'Bargaining Unit Size'[Report Month] = EARLIER('Bargaining Unit Size'[Report Month]),

    FILTER('Bargaining Unit Size', 'Bargaining Unit Size'[Local Code]= SELECTEDVALUE('Bargaining Unit Size'[Local Code])

)

[ewhaley]

the Earlier did not work - see someone else's comment above. Thank you!

[v-sgandrathi]

Hi @ewhaley,

 To create a proper date table, go to the Modeling tab, click on New Table, and enter the following DAX

DAXCopyEditDate = 
ADDCOLUMNS (
    CALENDAR (DATE(2023, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

In the Model view, create a relationship by linking the 'Bargaining Unit Size'[Report Month] column to the 'Date'[Date] column. Ensure that the Report Month column is of Date type. If it is not, convert it to the correct type using Power Query Editor before establishing the relationship.

To create the Month-over-Month (MoM) measure, go to the Modeling tab, select New Measure, and paste the following DAX formula.

DAXCopyEditMoM Change = 
VAR SelectedLocal = SELECTEDVALUE('Bargaining Unit Size'[Local Code])
VAR CurrentValue =
    CALCULATE(
        SUM('Bargaining Unit Size'[Current BU Size]),
        'Bargaining Unit Size'[Local Code] = SelectedLocal
    )
VAR PreviousValue =
    CALCULATE(
        SUM('Bargaining Unit Size'[Current BU Size]),
        'Bargaining Unit Size'[Local Code] = SelectedLocal,
        DATEADD('Date'[Date], -1, MONTH)
    )
RETURN
IF(
    ISBLANK(PreviousValue),
    BLANK(),
    DIVIDE(CurrentValue - PreviousValue, PreviousValue)
)

To enable users to filter by location, add a Slicer to the report by dragging the Local Code field from the 'Bargaining Unit Size' table into a slicer visual. Then, insert a Table or Line Chart to display the Month-over-Month (MoM) change. For the chart, use 'Date'[Date] as the axis and the MoM Change measure as the value.

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Thank you.

[ewhaley]

there are no errors but it still isn't working properly ...trying to see how to attach a pbix here...don't see any options for attaching an actual file

[v-sgandrathi]

Hi @ewhaley,

To Attach the Pbix.file just go to the reply option and click on it you will see the browse option as below screenshot. 
Here you can attach the file.

or I'm providing the link you can refer:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

You can upload your data into googledrive or drop box and you can paste the link here.

Thank you.

[v-sgandrathi]

HI @ewhaley,

After looking and reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Regards

Communtiy Support Team_Sahasra.

[ewhaley]

thank you. this didn't work because it is simply using the same sum BU size that already exists. What I am trying to do is for each Local, calculate the MOM # change in the BU size and the MOM % change. So, for A101, for instance, it went from 120 in Jan to 180 in Feb to 190 in Mar. So the MOM # for Feb would be +60 and for Mar would be +10. MOM% for Feb would be 60/120 or 50%. In Mar it would be 10/180 or 5.6%

I can get those measures to work for the entire data set as an overall change in the total sum from month to month, but not at the Local level. 

[v-sgandrathi]

Hi @ewhaley,

Hi,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Than you.

[FBergamaschi]

Hi ewhaley,

can you attach the pbix for inspection? In this way I could understand better your DAX. EARLIER is not recommended anymore since years and a variable will make everything clearer and simpler but to make sure I am getting this right and provide you the best support, a pbix with the measures involve in the reuqest at work would greatly help. Best, FB

[FBergamaschi]

Hi ewhaley,

can you attach the pbix for inspection? In this way I could understand better your DAX. EARLIER is not recommended anymore since years and a variable will make everything clearer and simpler but to make sure I am getting this right and provide you the best support, a pbix with the measures involve in the reuqest at work would greatly help. Best, FB

[v-sgandrathi]

HI @ewhaley,

Thank you for your question and Thank you @SamsonTruong for providing an answer to the query.

You are correct that your first variation works due to the row context created by SUMX.
In the second version, using EARLIER() within CALCULATE() is problematic because EARLIER() depends on row context, which isn't present in that structure. This is likely why you're not getting the expected results.
To resolve this, you can try the following:

Replace EARLIER() with a function like MAX() or rely on the filter context from the report visuals.

Use SELECTEDVALUE() to capture the user's slicer input and apply that conditionally within CALCULATE.

Optionally, set a fallback (like BLANK() or a default local code) in case no single value is selected.

This approach ensures the measure behaves dynamically while avoiding context transition issues.

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Regards

Communtiy Support Team_Sahasra.

[v-sgandrathi]

Hi @ewhaley,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[SamsonTruong]

Hi @ewhaley ,

Please try the following adjusted DAX for your 2nd measure and let me know if it achieves your desired result:

SUM BU Size :=
VAR SelectedLocal = SELECTEDVALUE('Bargaining Unit Size'[Local Code], BLANK())
RETURN
CALCULATE(
    SUM('Bargaining Unit Size'[Bargaining Unit Size]),
    FILTER(
        'Bargaining Unit Size',
        'Bargaining Unit Size'[Report Month] = EARLIER('Bargaining Unit Size'[Report Month]) &&
        (
            ISBLANK(SelectedLocal) || 'Bargaining Unit Size'[Local Code] = SelectedLocal
        )
    )
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session