Discard first year from list of value

[Pier2]

Hello,

I want to discard the year of the first data entry but keep the rest.

As a first sted, I've tried to filter the year (integer) as a minimum value and add 1. 

VAR varPremiereAnne= FILTER(MIN(tblEntrepriseDonnees[intAnnee]),USERELATIONSHIP(tblEntreprise[KEntreprise],tblEntrepriseDonnees[FKEntreprise]) ) +1

  And then Calculate or Filter to filter the values, but never get close to solve this one.

VAR ft= 
CALCULATE( VALUES(tblEntrepriseDonnees[sglCoutCapitauxPropres]),USERELATIONSHIP(tblEntreprise[KEntreprise],tblEntrepriseDonnees[FKEntreprise]),tblEntrepriseDonnees[intAnnee]>=varPremiereAnne)

How can I filter that information properly.
Thanks.

[Anonymous]

Hi, @Pier2 

Based on the first expression you provided, it appears that you are using the filter function in your variables, which returns a table. What you actually need is a constant value, not a table. Here's one change to your expression:

VAR varPremiereAnne = CALCULATE(MIN(tblEntrepriseDonnees[intAnnee]), USERELATIONSHIP(tblEntreprise[KEntreprise],tblEntrepriseDonnees[FKEntreprise])) + 1

This variable will calculate the smallest year. Then filter your dataset using the DAX expression you provided below:

VAR ft= 
CALCULATE( VALUES(tblEntrepriseDonnees[sglCoutCapitauxPropres]),USERELATIONSHIP(tblEntreprise[KEntreprise],tblEntrepriseDonnees[FKEntreprise]),tblEntrepriseDonnees[intAnnee]>=varPremiereAnne)

In addition to this, you can also filter out the first year through the Filter panel.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pier2]

Hello @Anonymous , 

Thanks 🙂 for your help. The first bloc of code solved the problem. It returns the right year. 

The second bloc still fails by returning nothing. I play a bit with the code.  Change Values for SUM, MIN, MAX and others ways around, but results are wrong and shows invalid years. 

One of the formula I've try: 

CALCULATE( SUM(tblEntrepriseDonnees[sglCoutCapitauxPropres]),
     FILTER( tblEntrepriseDonnees, 
     tblEntrepriseDonnees[intAnnee]>=varPremiereAnne),USERELATIONSHIP(tblEntreprise[KEntreprise],
    tblEntrepriseDonnees[FKEntreprise]))

Must be close of a solution. 

Any idea?

[Anonymous]

Hi, @Pier2 

Maybe you can try adding an ALL to your expression, the new expression is as follows:

CALCULATE(
    SUM(tblEntrepriseDonnees[sglCoutCapitauxPropres]),
    FILTER(
        ALL(tblEntrepriseDonnees),
        tblEntrepriseDonnees[intAnnee] >= varPremiereAnne
    ),
    USERELATIONSHIP(tblEntreprise[KEntreprise],tblEntrepriseDonnees[FKEntreprise])
)

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pier2 · Accepted solution]

Not the right move. ALL removes any filters. Then SUM addds up every values it finds on its way, event those not related to enterprise. 

In fact, each time a put a filter, results are wrong.