Time intellegence an relative date slicer

[Ritaf]

Hi all, 

I am trying to change my date slicer in report for relative date (This year for example) to make the dates progress automaticly in every refresh. This action causes a problems with all my measures of YOY , the result they are showing is all last year and not a same period.

How Can i handle with this? (the function that i use now is SAMEPERIODLASTYEAR (DATEKEY).

thanks a lot Rita.

[AiolosZhao]

Hi @Ritaf ,

Do you mean you are changing the default selection for your date slicer automatically in every refresh?

If yes, may I know how do you do that? If you can give a sample data that will be better.

Thanks.

Aiolos Zhao

[Ritaf]

No, the only way  i know to make a slicer to move date in every refresh date is using "relative date filter" ....and it causes my last year measures troubles

[amitchandak]

In case there is no date selected. Or you do not stop you calendar on a date most of the time intelligence function will not should correct YTD or Last Year YTD

You can use sampleperiodlastyear or any of these

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

[Ritaf]

The date is sellected , but isnstead of from date1 to date2 , is "this year"....

[amitchandak]

In case you are using a date slicer, YTD should work with end date.

Another way to control it till the end of fact date

LY = 
var _max = maxx('Fact','Fact'[Date])
var _max1 =maxx(filter('Date Table','Date Table'[Date]<=_max),'Date Table'[Month No])
return
 CALCULATE(sum('Fact'[value]),DATESYTD(dateadd('Date Table'[Date],-1,YEAR)),'Date Table'[FY Month No]<=_max1)


TY = 
var _max = maxx('Fact','Fact'[Date])
return
CALCULATE(sum('Fact'[value]),DATESYTD('Date Table'[Date]),'Date Table'[Date]<=_max)

[Ritaf]

It isn't working

[amitchandak]

@Ritaf , Your last year measure id YTD or complete. Can you share calculation, what exactly is wrong in term of a calculation?

If possible share a pbix after removing sensitive information.  With expected output

[Ritaf]

i have tried to filter daates table by this way:

OrdersQuontityLY = CALCULATE([OrdersQuonatity],SAMEPERIODLASTYEAR('datetable[DateKey]),FILTER('datetable,'[DateKey]<=max('orderstable[orderdate])))

bit it shows me a"blank value"

[Ritaf]

Hi , it seems very basic calculation.

I gave a ordersNum which calculates with formula

OrdersQuonatity = DISTINCTCOUNT('ordTable'[orderid])

and Paralel period in Last year oeders Quontity with a formula :

OrdersQuontityLY = CALCULATE([OrdersQuonatity],SAMEPERIODLASTYEAR('dateTable'[DateKey]))

When i use slicer from date to date it works , as i changed it to "this year"

it xalculates all last year and not  a paralel period

[Ashish_Mathur]

Hi,

In your Data Table, what is the last date you see?  If it is December 31, 2020, then the SAMEPERIODLASTYEAR() function will calculate sales from January 1, 2019 to December 31, 2019.  Once way out could be to progess the date column in the Calendar table with each passing day.  Ideally, the Calendar Table, should be created writing this formula (Modelling > New Table)

=CALENDAR(MIN(Data[Date]),TODAY())

Hope this helps.

[Ritaf]

Hi,

"In your Data Table, what is the last date you see?  If it is December 31, 2020, then the SAMEPERIODLASTYEAR() function will calculate sales from January 1, 2019 to December 31, 2019." - I got it from the beginning.

The question is if this is the way to handle it without changing a Date Table?

[amitchandak]

@Ritaf , You have already tried these without success ?

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

[Ashish_Mathur]

Hi,

There could be a solution.  The logic should be something like - "Compute sales till the last date available in the Sales data table rather than the last date available in the Calendar Table".  If you are unable to create this logic via DAX formulas, then share the link from where i can download your PBI file and show the exact expected result as well.

[Ritaf]

Hi ,

the lik for pbix is :

https://drive.google.com/file/d/1LpfUV3qhI9iPiFya7emqdJSsWAIwdFM7/view?usp=sharing

[amitchandak]

PFA, Seem like YTD and LYTD calculation are correct. But no dates are shown based on the relative date filter. I have done a few changes like marking calendar Dates. Added new formula's

[Ritaf · Accepted solution]

Thanks all, 

I found the solution by myself.

Added to the dates table flag column, wich gets value 1 when datekey is before today,

And filtered the report by this column.

[Ritaf]

i see that the all months displayed at your table too.

I begining to think that there is no solution .

[Ashish_Mathur]

Hi,

Write these 2 measures

Date till which sales are recorded = MAX(Sheet1[oRDERdate])

QuantityOrdersLY = CALCULATE([QuantityOrders],DATESBETWEEN('תאריכים'[DateKey],EDATE(MIN('תאריכים'[DateKey]),-12),EDATE([Date till which sales are recorded],-12)))

Drag the second measure to a card visual.  The result will be blank because sales in the current year are till March 17, 2020.  The previous year therefore is January 1, 2019 to March 17, 2019 whereas in your sheet1, there is no data for the period January 1, 2019 to March 17, 2019.