Forum Discussion

Pier2's avatar
Pier2
Icon for Resolver I rankResolver I
2 years ago
Solved

Translate calculated column into measure

Hello,

I'm trying to translate a calculated column into a measure.
Years are integers in the database.

 

VAR idEntreprise = CALCULATE(VALUES( tblEntrepriseDonnees[FKEntreprise])) //idEnterprise
VAR anneeCourante = CALCULATE(VALUES( tblEntrepriseDonnees[intAnnee])) // currentYear


VAR valeurAnneePrecedente = //valuePreviousYear
    PRODUCTX( FILTER(tblEntrepriseDonnees,tblEntrepriseDonnees[intAnnee]= anneeCourante-1 && tblEntrepriseDonnees[FKEntreprise]=idEntreprise),
        tblEntrepriseDonnees[sglCapitauxPropres]*tblEntrepriseDonnees[lngMultiple])


VAR valeurAnneeCourante =   // valueCurrentYear
      PRODUCTX( FILTER(tblEntrepriseDonnees,tblEntrepriseDonnees[intAnnee]= anneeCourante && tblEntrepriseDonnees[FKEntreprise]=idEntreprise),
        tblEntrepriseDonnees[sglCapitauxPropres]*tblEntrepriseDonnees[lngMultiple])

RETURN


IF( ISBLANK(valeurAnneePrecedente),
    BLANK(),
    (valeurAnneePrecedente+valeurAnneePrecedente)/2
    )

 

The calculated column do fine but in the measure, the moment I put -1 to the formula, to get the value of the previous year, it fails.

How can I fit it?

Need Help
  • Pier2's avatar
    Pier2
    2 years ago
    VAR idEntreprise = SELECTEDVALUE(tblEntrepriseDonnees[FKEntreprise])  

VAR anneeCourante = SELECTEDVALUE(tblEntrepriseDonnees[intAnnee]) 

VAR valeurAnneePrecedente =
    CALCULATE(
        SUMX(
            FILTER(tblEntrepriseDonnees, tblEntrepriseDonnees[intAnnee] = anneeCourante - 1 && tblEntrepriseDonnees[FKEntreprise] = idEntreprise),
            tblEntrepriseDonnees[sglCapitauxPropres] * tblEntrepriseDonnees[lngMultiple]
        ),
        ALLEXCEPT(tblEntrepriseDonnees, tblEntrepriseDonnees[FKEntreprise], tblEntrepriseDonnees[intAnnee])
    )

VAR valeurAnneeCourante =
    CALCULATE(
        SUMX(
            FILTER(tblEntrepriseDonnees, tblEntrepriseDonnees[intAnnee] = anneeCourante && tblEntrepriseDonnees[FKEntreprise] = idEntreprise),
            tblEntrepriseDonnees[sglCapitauxPropres] * tblEntrepriseDonnees[lngMultiple]
        ),
        ALLEXCEPT(tblEntrepriseDonnees, tblEntrepriseDonnees[FKEntreprise], tblEntrepriseDonnees[intAnnee])
    )

RETURN
    IF(
        ISBLANK(valeurAnneePrecedente),
        BLANK(),
        (valeurAnneeCourante + valeurAnneePrecedente )/2
    )

2 Replies

  • Pier2's avatar
    Pier2
    Icon for Resolver I rankResolver I
    2 years ago
    VAR idEntreprise = SELECTEDVALUE(tblEntrepriseDonnees[FKEntreprise])  

VAR anneeCourante = SELECTEDVALUE(tblEntrepriseDonnees[intAnnee]) 

VAR valeurAnneePrecedente =
    CALCULATE(
        SUMX(
            FILTER(tblEntrepriseDonnees, tblEntrepriseDonnees[intAnnee] = anneeCourante - 1 && tblEntrepriseDonnees[FKEntreprise] = idEntreprise),
            tblEntrepriseDonnees[sglCapitauxPropres] * tblEntrepriseDonnees[lngMultiple]
        ),
        ALLEXCEPT(tblEntrepriseDonnees, tblEntrepriseDonnees[FKEntreprise], tblEntrepriseDonnees[intAnnee])
    )

VAR valeurAnneeCourante =
    CALCULATE(
        SUMX(
            FILTER(tblEntrepriseDonnees, tblEntrepriseDonnees[intAnnee] = anneeCourante && tblEntrepriseDonnees[FKEntreprise] = idEntreprise),
            tblEntrepriseDonnees[sglCapitauxPropres] * tblEntrepriseDonnees[lngMultiple]
        ),
        ALLEXCEPT(tblEntrepriseDonnees, tblEntrepriseDonnees[FKEntreprise], tblEntrepriseDonnees[intAnnee])
    )

RETURN
    IF(
        ISBLANK(valeurAnneePrecedente),
        BLANK(),
        (valeurAnneeCourante + valeurAnneePrecedente )/2
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,Pier2 

       

      We are glad that you have found a solution and are willing to share it, and accept your post for a solution, which is very helpful to our community, and community members who have the same problem as you will find a solution faster. Thank you for your contribution to the community and wish you all the best in your work.

       

      Best Regards,

      Leroy Lu