Syntax conditional sum with filter on measure

[M666]

I need to sum the values over a specific period, namely last month. 

The values are in one table, the calendar details in another, one of the fields available is YYYY/MM in a text format. After several trial&errors, I came up with a measure to identify and format last month as:

LastMonth: if(Num(Month(Today()))=1,

Text(Year(today())-1&'/')&'12',
Text(Text(Year(Today())&'/')&Text(Num(Month(Today())-1,'00'))))

This seems to give the expected and desired outcome, in this case (writing in november '21) 2021/10. 

I then wanted to use this outcome as a filter for my sum measure, the totals for my values over last month with this measure:

Sum({<YearMonth={LastMonth}>} InvoicedQ)

I keep getting 0 (zero) as a result though. 

Anyone please recommend alternative or correct my thinking. 

thanks, 

M

[BenjaminT]

Hi,

Do you have the expression used for YearMonth field? The reason I ask is that even though it displays like '2021/10', it is possible that its actual value is a date (possibly the first day of the month), but it is set to display in 'YYYY/MM' format. This might cause the issue when you try to compare to LastMonth which is a text string.

If that was the case, you could try using the following for LastMonth, which similarly sets the underlying date as the first of the month but displays just the Year and Month (plus it is also a bit cleaner than the if statement you have).

Date(MonthStart(today(),-1), 'YYYY/MM')

I hope this helps you get to a solution

Many thanks

Ben

[M666]

Hello Ben,

Thanks for your reaction. I agree your recommended alternative would be lots cleaner and I've tried it. The reference field is however text (tested). When I type in the condition ="2021/10" I do get the expected outcome. I don't get the logic or syntax I'd need to apply to make this condition dynamic. 

cheers,

M

[BenjaminT]

Is LastMonth a field or a variable? If you store as a variable you could try this:

Sum({<YearMonth={'$(LastMonth)'}>} InvoicedQ)

Whilst editing your expression, take a look at the expression evaluator at the bottom. It should have replaced the variable name with the value, this is a good sign that it will work.

Thanks

 Ben

[M666]

I've created a variable LastMonth (vLastMonth) using 

if(Num(Month(Today()))=1,

Text(Year(today())-1&'/')&'12',
Text(Text(Year(Today())&'/')&Text(Num(Month(Today())-1,'00'))))

as logic to define vLastMonth. 

When I then modify my measure and refer to vLastMonth, the expression evaluator shows this formula, not the outcome of the formula. It's not clear what you meant in previous response. Should I expect to see 2021/10 as I type the variable in my expression or should I see the formula which defines the variable while typing it in my expression. 

[BenjaminT · Accepted solution]

Hi,

Yes I would be looking for the variable name to show the value rather than the formula in the expression evaluator. Do you have an equals (=) sign at the start of your variable expression? If not, try adding one. This will make the variable evaluate to a value within your set analysis.

Thanks

Ben

[M666]

Well, that appeared to have worked Ben. I added the = sign in front of the variable, got the value in the expression evaluator while typing the formula and the expected result as outcome. All good. I'll still have a follow up with the people having built the model to try understand why the more regular date functions don't seem to work, but for now I can proceed. many thanks!