Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to make a dashboard page that reference the current month always. I created a table with the header labeled 'Cur Mth' and one row member 'Feb'. I have the data table with titles Month, Year, Account, Actual Am2.
sum({$<Year={2016}>,Month={$(Cur Mth)}},[Actual Amt2])
It says the expression is correct, but I am not getting any data.
If i use just the year, it works. sum({$<Year={2016}>}[Actual Amt2])
I can't get adding the 2nd criteria to work. Any suggestions.
What was simple in excel is driving me nuts here.
Months are usually stored as a number (so 2 for February) and showing as text (or number, depending on what you want).
I suggest you try again, but now with a number instead.
Alternatively, if you want to use the current month, you can try to match it with the result of month(today()).
Peggy,
you first need to check the values of Month field. Do they show a text representation like 'Feb'?
Then you should be able to use a dollar sign expansion like Onno suggested:
=sum({$<Year={2016}>,Month={'$(=Month(Today()))'} >} [Actual Amt2])
Though this would return Mar, not Feb.
If you've created a table in your model with a field named 'Cur Mth', and a single record, you can also use
=sum({$<Year={2016}>,Month={'$(=Only([Cur Mth]))'} >} [Actual Amt2])
Hope this helps,
Stefan
i wanted to be able to control the date range because our company period ends on a date other than the end of a calendar month. Plus when I publish the report the month following period close and I thought the 'Today' function would change to the month you are in instead of the month just closed. My data has Month as 'Jan',Feb', ''Mar', etc... But I also have a Period column added referencing the date as a number.
Thanks. I'll give the 'Only' function a try
I haven't logged in yet to try this, but what is the rule for using the accent mark in front of the $ sign. Same with the (). can you refer to a good source that helps explain how to write expressions with multiple criteria?
It didn't work. Any other suggestions???
Hi Peggy,
Try below exp
sum({$<Year={2016},Month={'$(Cur Mth)'}>} [Actual Amt2])
Hi Peggy
Settings>VariableOverview>Add>
Variable Name :CurrYear
Select CurrYear and write Definition section =Year(today())
Add>
Variable Name :CurrMonth
Select CurrMonth and write Definition section =Month(today())
Controlling :Add two new SheetObjects>TextObject>Check variables are correct.
Then,
sum({<Year={'$(CurrYear)'},Month={'$(CurrMonth)'}>}[Actual Amt2])
İf you want to don't effect any filters use "1"
sum({1<Year={'$(CurrYear)'},Month={'$(CurrMonth)'}>}[Actual Amt2])
Peggy,
here is a short introduction how to use set modifiers:
In general, I use single quotes around field values in the set modifier value list, this also handles values with e.g. spaces correctly (so, you may be able to use a set modifier without single quotes like {2015,2016} , but you may experience issues in some cases).
See also
Why is it called Set Analysis?
The Magic of Dollar Expansions
Hope this helps,
Stefan