Set Analysis - Advanced Search

[Ed]

Hello,

I am facing quite an issue right now. I want to calcul the sum of my sales last year on the current month and fix this amout on my dashboard so that it doesn't move. The ultimate goal would be that I directly see this amout when I open my dashboard.  I keep putting this formula upside down, I can't find the correct one 😞 Help

Sum({1<MyYear={"=$(=Year(Today())"} , MyMonth={"=$(=Month(Today()))"}}>} [Sales])

Also I tried to put a variable instead of Year(Today()) and Month(Today() but it still didn't work.
Also MyMonth and MyYear are the same type 1,2,3,...,12 for months and 2016,2017,2018 for years

Thanks

Ed

[sunny_talwar]

Remove the = sign before the $ in your set modifier

Sum({1<MyYear = {"$(=Year(Today()))"}, MyMonth = {"$(=Month(Today()))"}>} [Sales])

[Ed]

Hi,

Thanks for the quick response, unfortunately it doesn't work. It displays 0 for the measure in KPI visualization for this year. I should have 393.  I would also like to have last year and not this year, where should I put the -1 ?

I don't calculate MyMonth and MyYear, the both fields comes from an excel sheet, they are allready like this

[sunny_talwar]

Few questions

1) This gives you 0 for the current year?

Sum({1<MyYear = {"$(=Year(Today()))"}, MyMonth = {"$(=Month(Today()))"}>} [Sales])

2) How exactly do you calculate MyYear and MyMonth in the script? Can you share the script code used to create these fields?

3) It might be easier if you are able to share a sample to troubleshoot this better

[Ed]

1) yes it gives me 0 for the current year and it shouldn't
2) I don't calculate them, I import them directly from excel sheet
Mois is MyMonth, Annee is MyYear and Nombre Intervention is sales
This is the load statement but my problem is within the measure I am trying to create
INTERVENTIONS :
LOAD
"Nombre Intervention",
Annee,
Mois
FROM [lib://Data/RequeteSQLMichel2.2.xlsx]
(ooxml, embedded labels, table is Feuil1) ;

[sunny_talwar]

Can you check if one of the two below gives you a number other than 0?

Sum({1<Annee = {"$(=Year(Today()))"}>} [Nombre Intervention])

or

Sum({1<Mois = {"$(=Month(Today()))"}>} [Nombre Intervention])

My guess is that the first one might work, but the second one might not... but let's see what you get? 

[Ed]

You are right. The first line works but not the second one

[sunny_talwar]

Can you share a filter object for Mois field? I want to see what format does it have? Is it Jan, Feb or is it 1, 2,... or is it Jan-2018, Feb-2018, ... etc

[Ed]

Sure

[sunny_talwar · Accepted solution]

Try this

Current Year

Sum({1<Annee = {"$(=Year(Today()))"}, Mois = {"$(=Num(Month(Today())))"}>} [Nombre Intervention])

 Previous Year

Sum({1<Annee = {"$(=Year(Today())-1)"}, Mois = {"$(=Num(Month(Today())))"}>} [Nombre Intervention])