Comparision graph issue (Current Year Nov and Dec months data dislaying from Previous year data)

[Vinothsusai]

Hello,

I faced a issue on graph Cout column N and N-1 comparision that displays current year Nov and Dec months data is displaying from previous year.

Here my table(filtered only for 2020 and 2019 records). the table is in first page and graph in second page(drill through filter - Resource). i just navigate to graph  by drill through the record in first page. if i drill through date on year 2020 , the graph will compare current year 2020 and last year 2019. if i drill through 2019, the graph will compare current year 2019 and last year 2018.

Date	Type	Resource	Description	Quantity	Prix	Cout	Ventes
1/29/2019 23:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	8	600.00 €	600.00 €
1/31/2019 23:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	10	750.00 €	750.00 €
2/19/2019 23:00	Acompte				106.00 €		106 €
3/19/2019 23:00	Acompte				106.00 €		106 €
3/27/2019 23:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	7.5	562.50 €	562.50 €
4/19/2019 22:00	Acompte				106.00 €		106 €
4/24/2019 22:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	6	450.00 €	450.00 €
5/19/2019 22:00	Acompte				106.00 €		106 €
5/21/2019 22:00	Temps	SOCIETE FLEXMIND	147 - CAC: mission générale	4	300.00 €	300.00 €
6/19/2019 22:00	Acompte				106.00 €		106 €
6/19/2019 22:00	Temps	SOCIETE FLEXMIND	000 - Avant-vente	5	375.00 €	375.00 €
7/16/2019 22:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	3	225.00 €	225.00 €
7/19/2019 22:00	Acompte				106.00 €		106 €
8/14/2019 22:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	5	375.00 €	375.00 €
8/19/2019 22:00	Acompte				106.00 €		106 €
9/19/2019 22:00	Acompte				106.00 €		106 €
10/19/2019 22:00	Acompte				106.00 €		106 €
11/11/2019 23:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	4.5	337.50 €	337.50 €
11/19/2019 23:00	Acompte				106.00 €		106 €
12/19/2019 23:00	Acompte				106.00 €		106 €
12/19/2019 23:00	Temps	SOCIETE FLEXMIND	103 - Situation	1	75.00 €	75.00 €
12/30/2019 23:00	Acompte				########		5,000 €
12/31/2019 23:00	Forfait/Frais	SOCIETE FLEXMIND	3	326.00 €	326.00 €
1/29/2020 23:00	Temps	SOCIETE FLEXMIND	103 - Situation	7	525.00 €	525.00 €
1/31/2020 23:00	Acompte				409.00 €		409 €
2/26/2020 23:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	5.5	412.50 €	412.50 €
2/29/2020 23:00	Acompte				409.00 €		409 €
3/23/2020 23:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	3	225.00 €	225.00 €
3/31/2020 22:00	Acompte				409.00 €		409 €
4/22/2020 22:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	4	300.00 €	300.00 €
4/30/2020 22:00	Acompte				543.00 €		543 €
5/19/2020 22:00	Temps	SOCIETE FLEXMIND	104 - Comptes annuels	6	450.00 €	450.00 €
5/31/2020 22:00	Acompte				409.00 €		409 €
6/30/2020 22:00	Acompte				409.00 €		409 €
7/31/2020 22:00	Acompte				409.00 €		409 €
8/31/2020 22:00	Acompte				409.00 €		409 €
9/30/2020 22:00	Acompte				409.00 €		409 €
10/29/2020 23:00	Temps	SOCIETE FLEXMIND	102 - Tenue de journaux	1	75.00 €	75.00 €
10/31/2020 23:00	Acompte				409.00 €		409 €

 i used my formaula dax below

Current year

Current Year =

var date1=

MAX(msdyn_actuals[msdyn_documentdate]) return

CALCULATE (

[Measure Total of type Cout],

FILTER(msdyn_actuals, YEAR(msdyn_actuals[msdyn_documentdate]) = YEAR(date1)))

Last Year

Last Year =

var date1=

MAX(msdyn_actuals[msdyn_documentdate])

RETURN

CALCULATE (

[Measure Total of type Cout],

FILTER(msdyn_actuals, YEAR(msdyn_actuals[msdyn_documentdate]) = YEAR(date1)-1))

Please advise.

Thanks

Vinoth

[Anonymous · Accepted solution]

Hi @Vinothsusai ,

The reason for the error may be because your dax function uses "var date1=MAX(msdyn_actuals[msdyn_documentdate])".

It will filter out the maximum date. The maximum date of November and December is the data of 2019, so your Current Year measure will return the data for 2019 in November and December.

Below is my solution.

1.Create a new table using DAX.

Table = DISTINCT(SELECTCOLUMNS('msdyn_actuals',"Year",YEAR([Date])))

2.Measures are as follows.

Current Year = 
var date1=
SELECTEDVALUE('Table'[Year]) return
CALCULATE (
[Measure Total of type Cout],
FILTER(msdyn_actuals, [year] = date1))

Last Year = 
var date1=
SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE (
[Measure Total of type Cout],
FILTER(msdyn_actuals, YEAR(msdyn_actuals[Date]) = date1-1))

You can check more details from here.

Best Regards,

Stephen Tao

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