measure without context in summarised table

[jimbob2285]

Hi

I've created a measure to show sales as at a certain date (month) , which is selected by the user via a slicer, hence the need for a measure rather then a column, as columns aren't reactive to slicers, they're only calculated and data load

This is a common requirement from our accounts team, they want to be able to wind a report back to look at figures at a certain point in time, end of last month, end of last year, etc.

I've tried writing the measure a couple of ways, but both result in the same issue

_Value_At_SelectedMonth_1 = IF(MAX(Sales[Month_YYYYMM]) <= SELECTEDVALUE('Calendar'[Month_YYYYMM]), SUM(Sales[Value]), 0)

_Value_At_SelectedMonth_2 = 
VAR MyFilterTable = SUMMARIZE(FILTER(ALL('Calendar'), 'Calendar'[Month_YYYYMM] <= SELECTEDVALUE('Calendar'[Month_YYYYMM])), 'Calendar'[Month_YYYYMM])
RETURN
IF(MAX(Sales[Month_YYYYMM]) IN MyFilterTable, SUM(Sales[Value]), 0)

The challenge I have and keep coming back to each time I try to do this, is that it only works when the date is in the table visual, as soon as I take the date out, the measure no longer works.  I get that measure work on context and the lack of date in the table visual is removing the context, so I understand why it;s not working, but is there another way to achieve this?

can the measure be written a different way or are calculation groups reactive to slicers, i woudl naturally achieve this with a column, as I have doen with the 'Value at Sep' column, but it needs to be a measure in order to react to the slicer.

Is this just not achievabel, or am I missing omething obvious?  I've shared a sample file: Value at Month 

Thanks

Jim  

[V-yubandi-msft]

Hi @jimbob2285 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft]

Hi @jimbob2285 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @jimbob2285 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[V-yubandi-msft]

Hi @jimbob2285 ,

We found the solution shared by @lbendlin , to be appropriate and aligned with the original requirement. Could you please confirm if this resolved your issue?

If it worked as expected, we encourage you to mark the response as 'Accept as Solution' so that others with similar queries can benefit from it as well. If the issue still persists, feel free to share additional details so we can continue assisting you. Additionally, review  the PBIX file shared by @Ashish_Mathur .

Please note, we were unable to download the file you attached from our end.

@lbendlin  & @Ashish_Mathur ,Thank you for your ongoing valuable contributions and involvement in the community.

Regards,

Yugandhar.

[Ashish_Mathur]

Hi,

Not sure of what you want but see the attached file.

[lbendlin · Accepted solution]

Something like this?

V = CALCULATE(sum(Sales[Value]),TREATAS(VALUES('Calendar'[Date]),Sales[Date]))