The monthly measure isn’t working when not all values from the current month are selected.”

[julsr]

Hello everyone,

I have a table with the following structure:

ID | Budget | Date | SiteID

The values in the Budget column are repeated as often as the IDs have the same SiteID for the month. I want to retrieve the unique value per SiteID per month, and then summarize it by month to get the ‘Monthly Value’ for the entire company. Additionally, I need to calculate the Yearly value. This is the measure I'm using:

SUMX(

    VALUES(Table[SITEID]),  -- Iterate over each unique SiteID

    CALCULATE(

        MAX(Table[BUDGET]),  -- Get the MAX value for each SiteID

        FILTER(

            ALL(Table[Date]),  -- Remove any date filters applied

            YEAR(Table[Date]) = YEAR(MAX(Table[Date])) &&  -- Ensure calculation is for the entire year of the selected date

            MONTH(Table[Date]) = MONTH(MAX(Table[Date])) &&  -- Ensure calculation is for the entire month of the selected date

           Table[SITEID] = EARLIER(Table[SITEID])  -- Ensure calculation is per SiteID

        )

    )

)

The issue is that I’m using a measure that works. Still, when I select a specific day of the current month, it doesn’t show all the values correctly for both the Monthly and Yearly calculations (it filters out the values that are not in the filter anymore). How can I modify the column to calculate the monthly and yearly values while ensuring they are unique to each SiteID, regardless of the selected dates?

Thanks!

[Anonymous]

Hi @julsr ,

The file link can’t open.

Based on the description, try using the following DAX formula.

SUMX(
    VALUES(Table[SITEID]),  -- Iterate over each unique SiteID
    CALCULATE(
        MAX(Table[BUDGET]),  -- Get the MAX value for each SiteID
        FILTER(
            ALL(Table[Date]),  -- Remove any date filters applied
            YEAR(Table[Date]) = YEAR(Table[Date]) &&
            MONTH(Table[Date]) = MONTH(Table[Date]) && 
           Table[SITEID] = EARLIER(Table[SITEID])
        )
    )
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @julsr 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[julsr]

Sure, find dummy data here: LINK .

Thank you!