Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

calculating seniority banking

I have table Fact_an

Code_produitCode_Gestionnaire_ProdID_DateFicheAgentNBANRef_Idbanking seniority
12531235604/01/202020011
8596965896505/01/202060021
2586396325806/01/2020180032
256321235607/01/2020540042
1253965896508/01/20201620052
859696325809/01/20204860015
258631235610/01/202014580035
25632965896511/01/202043740043
12531235604/01/201912318
8596965896505/01/201960028
2586396325806/01/2019180038
256321235607/01/201960048
1253965896508/01/201960057
859696325809/01/201960017
258631235610/01/201960037
25632965896511/01/201960047



banking seniority

banking seniorityName
1less than 1 year
2less than 2 year
3less than 3 year
4less than 4 year
5less than 5 year
6less than 6 year
7less than 7 year
8less than 8 year

 

I create a report and a new mausure AN. WHE A USER SELECT A YEAR AND A MONTH AND banking seniority, I need to calculate the count of the rows the last 12 of months banking seniority< banking seniority seelcted by the user

AN = 

VAR a = SELECTEDVALUE(Dim_DateFicheAgent[ID_DateFicheAgent])
VAR b =SELECTEDVALUE('Seniority banking'[banking seniority])
RETURN

    CALCULATE (
COUNTROWS(FILTER(Fact_AN;

     (Fact_AN[banking seniority]<=b && NOT ISBLANK (Fact_AN[banking seniority]))));
         DATESBETWEEN (
        Dim_DateFicheAgent[ID_DateFicheAgent];
        NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] ) ));
        LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] )

))

it returns errors wrong values. I put an example of the report here https://drive.google.com/file/d/1Ja3NevOm6i80uuS6lKPpHIBYaNQ2jee2/view?usp=drivesdk