Variation of days over days

[Anonymous]

Hi everyone!, i have two tables:

Table 2021 that contains 17 days = 1/1/2021 to 17/1/2021

and Table 2020 that bring all January.

I want the % variation of a value in that period but i dont want to take all January from Table 2020, just 17 days.

I use dax calculation for take the variaton:

PreviusYear =

Var Anio = MAX(Calendario[Año])

Var LYAnio = Anio-2

Return

CALCULATE(SUM('vw_generaciontipo'[Valor2]),Calendario[Año]=LYAnio)

--------------------------------------------------------------------------------

YEARTODAY =

Var Anio = MAX(Calendario[Año])

Var LYAnio = Anio -1

Return

CALCULATE(SUM('vw_generaciontipo'[Valor2]),Calendario[Año]=LYAnio)

---------------------------------------------------------------------------------

% Growth = IF([PY]==0,0,DIVIDE([YTD]-[PY],[PY],0)+1)

---------------------------------------------------------------------------------

This calculation are good, but how can i tell him that take just 17 days of January of 2020?

Any idea ?

Thanks!

[Anonymous]

Hi @Anonymous,
Can please share some dummy data with a similar data structure and expected results? It should help us clarify your data structure and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,
Xiaoxin Sheng

[AllisonKennedy]

@Anonymous  You need to add another filter to check that the day of month is less than or equal to today. Do you have a [Day] column in your date table? You can try the below, just switch [day] for the name of your column in your language.

PreviusYear =

Var Anio = MAX(Calendario[Año])

Var LYAnio = Anio-2

Var _Today = DAY(Today())

Return

CALCULATE(SUM('vw_generaciontipo'[Valor2]),Calendario[Año]=LYAnio, Calendario[Day] <= _Today)

[amitchandak]

@Anonymous , refer these examples with time intelligence

MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.