Reply
Occasional Visitor
Posts: 1
Registered: ‎01-12-2019

CALENDAR(date(Year(today())-1,1,1),today()) doesn't work for 2019. Please help!

Dear all

 

We were using the below measures for calcuating the Sales for current year vs previous year for 2017-18.  This was working perfectly fine for 2017-18. But the same is not working for 2018-19

 

DatesCalc = CALENDAR(date(Year(today())-1,1,1),today())

Sales_CY = CALCULATE([Total_Sales],FILTER(DatesCalc,year(DatesCalc[Date])=Year(today())))

Sales_PY = CALCULATE([Total_Sales],SAMEPERIODLASTYEAR(DatesCalc[Date]))

 

Currently we aren't able to view the 2019 data. The weird part here is this happens only for the Location based Sales data and not for the category based sales data. There is no change in the DB struture for 2019 vs 2018 r 2017 data. Any help here will be more appreciated. My org is not able to view the data Smiley Sad

 

PS: Since we have multiple DB's we had to group them and use a common measure to get the result.

 

Regards

Purnima

Super User
Posts: 2,250
Registered: ‎09-19-2016

Re: CALENDAR(date(Year(today())-1,1,1),today()) doesn't work for 2019. Please help!

Hi @Purnima_Pandey,

 

First of all check in the table view if you are abble to see the 2019 data, if not try to check the database. If you are abble to see the 2019 data in the table view see if you have any filter on your reports that is slicing the data for only 2018.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Posts: 3,943
Registered: ‎01-14-2017

Re: CALENDAR(date(Year(today())-1,1,1),today()) doesn't work for 2019. Please help!

Hi,

 

Try this new Table formula: DatesCalc = CALENDAR(MIN(Data[Date]),today()).  In this DatesCalc table, write this calculated column formula: Year=YEAR(Calendar[Date]).  Create a relationship from the Date column of the Data Table to the Date column of the DatesCalc table.

 

In your visual, drag the Year column from the Calendar Table.  Write these measures

 

Sales_CY = SUM(Data[Sales])

Sales_PY = CALCULATE([Sales_CY],SAMEPERIODLASTYEAR(Calendar[Date]))

 

Hope this helps.