cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ybolo Frequent Visitor
Frequent Visitor

Problem with date table and LY YTD

I have a problem.
I want to do two KPIs, one is a measurement this year and the other the same measurement the year before.

I would like to be able to compare them within the same kpi.

 

so target goals LastYear measure
indicator : This year mesure
Trand axis : Month

 

I try to use: Column = CALCULATE([total default guarantee];SAMEPERIODLASTYEAR(Recapoperation_observation[Date of entry])) to calculate lastYear measure.

 

but this message appears:

"A date column containing duplicate dates was specified in the call to function'SAMEPERIODLASTYEAR. This is not supported."

 

So i try to build to date table with that Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ([Date]; "YYYYYMMDD");
"Year"; YEAR ([Date]);
"Monthnumber"; FORMAT ([Date]; "MM");
"YearMonthnumber"; FORMAT ([Date]; "YYYYY/MM");
"YearMonthShort"; FORMAT ([Date]; "YYYYY/mmm");
"MonthNameShort"; FORMAT ([Date]; "mmm");
"MonthNameLong"; FORMAT ([Date]; "mmmm");
"DayOfWeekNumber"; WEEKDAY ([Date]);
"DayOfWeek"; FORMAT ([Date]; "dddd");
"DayOfWeekShort"; FORMAT ([Date]; "ddd");
"Quarter"; "Q" & FORMAT ([Date]; "Q");
"YearQuarter"; FORMAT ([Date]; "YYYYY") & "/Q" & FORMAT ([Date]; "Q")
)

so I linked the  new date table to my date column (date de saisie) in order to have only one entry per date.

Something I don't understand is happening:
When I use my new date column in a table there is no date for 2018 while on the table there are entries in 2018.

 

For example:

At first I had
01/01/2018 = 1
01/01/2018= 2
01/01/2018 = 3
02/01/18 = 5
02/01/18 = 6
02/01/18 = 7

 

With my new date column I have
01/01/2018 = 1
02/01/18 = 5

 

And in addition, dates are missing.

Anyone have any idea what's going on?

 

PS : 

screenshot1.png

 

screeshot2.png

 

Nomre défaut l'année en cours = Mesure.

Date de saisie = first table

Date = new table

 

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Problem with date table and LY YTD

Hi ybolo,

 

Could you share your table structure and relationship between 'Recapoperation_observation' and other tables?

 

Regards,

Jimmy Tao

ybolo Frequent Visitor
Frequent Visitor

Re: Problem with date table and LY YTD

screenshot4.png

 

Thanks for your answer