Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need help with defining set analysis in measure that refers to the user's selection.
The measure should show the amount of expenses of the current year only.
Selecting an expenses type, quarter, month, or date affect on the amount. Any selection in a different field, including selection of a different year, will have no effect on the amount.
This is what I get so far and didn't work perfectly:
Sum({1<Year={'$(vCurrYear)'}, QuarterID={*} MonthID={*}, ExpType={*}>}Amount)
Missing the Date field (should be all dates from the first day of vCurrYear).
Dataset for example without Date field:
Year | Month | ExpType | Budget |
2023 | 12 | aaa | 100 |
2023 | 12 | bbb | 150 |
2023 | 12 | ccc | 200 |
2024 | 1 | aaa | 200 |
2024 | 1 | ccc | 300 |
2024 | 1 | ddd | 400 |
2024 | 2 | bbb | 250 |
2024 | 2 | ccc | 300 |
2024 | 2 | ddd | 350 |
Year=2024 --> Amount=1800
Year=2024, Type=aaa --> Amount=200
Year=2023, Type=bbb --> Amount=250 (bbb of 2024)
Year=2023, Customer=[nevermind] --> Amount=1800 (amount of 2024)
Thanks!
Not sure exactly your situation without access to all fields but try using P function along with {1}
Sum({1<Year={'$(vCurrYear)'}, QuarterID=P(QuarterID), MonthID=P(MonthID), ExpType=P(ExpType)>}Amount)
Not sure exactly your situation without access to all fields but try using P function along with {1}
Sum({1<Year={'$(vCurrYear)'}, QuarterID=P(QuarterID), MonthID=P(MonthID), ExpType=P(ExpType)>}Amount)
In your set expression you have for example this: ExpType={*} (I believe you meant to write ExpType={"*"} ).This means that the expression will include all values in ExpType, and it will disregard any selections in this field.
If I understand you correctly, you want the opposite of this; you want to apply only selections in the fields you specified, and disregard selections in all other fields. The suggestion by @Digvijay_Singh hopefully works. I just thought I'd try to explain why your own attempt did not work.
Thanks! I also added the Date field as you mentioned and it's look fine