Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time Intelligence Measures with Dynamic Calendar Table

Hello Everyone! Hopeful for some help.   I have been having issues with my time intelligence measures: they either pull up no data (when data should be present) or incorrect data. Only one is worki...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    @EdenHutch

    See these formulas. But remember how your calendar goes to the end date of the year. You need to have some sort of filter on visual or page to stop the maximum date for the last few days or even for ytd if you want data until today.

    Pasados 30 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-30,Day))
Pasados 60 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-60,Day))
Pasados 90 días: CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-90,Day))
Pasados 120 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-120,Day))

O
Pasados 30 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales]),-30,DAY))  
Pasados 60 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales]),-60,DAY))  
Pasados 90 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales]),-90,DAY))  
Pasados 120 días: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales]),-120,DAY))  

Día detrás de las Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Hoy en día, CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]-Today())

Rolling 12 - CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 - CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales]),-12,MONTH))  

Rodar 3 hasta el mes pasado: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rodar 3 hasta los últimos 2 meses: CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))

Rodar 11 hasta después de 6 meses, CALCULATE(sum('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('Date'[Date],startOFMONTH(dateadd(Sales[Sales Date],6,month)),11,MONTH))

Rolling 3 - CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0
Rolling 3 - CALCULATE(distinctcount(Transation[Month-Year]),DATESINPERIOD('Date'[Date],today(),-3,MONTH))+0
Rolling 12 - CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 - CALCULATE(sum(Sales[Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
Ventas de MTD de 3 meses de retroceso: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

Ventas de MTD: CALCULATE(SUM(Sales[Sales]),DATESMTD('Fecha'[Fecha]))
ventas de MTD más importantes: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
último MTD (completo) Ventas - CALCULATE(SUM(Sales[Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
el año pasado MTD (completo) Ventas - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
el año pasado MTD Ventas - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
el último QTR mismo mes (completo) Ventas - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (fin de año) Ventas - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date]))))
MTD (último fin del año) Ventas - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

Ventas DE QTD: CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Fecha'[Fecha])))
Ventas de QTD: CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Ultima hasta la última cantidad de ventas QTD: CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Próximas ventas qtd ( CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
El año pasado mismo Sint. Ventas de QTD: CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
QTR finales - CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR - CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

YTD Ventas- CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales á CALCULATE(SUM(Sales[Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Ventas de última ytd: CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Ventas completas de last YTD: CALCULATE(SUM(Sales[Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Ultima hasta la última venta de YTD: CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Año detrás de Las Ventas (CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Año + 3 semanas detrás de Las Ventas - CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))
55 semanas detrás de Las Ventas -CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],365+12,Day))
Ventas completas de last YTD: CALCULATE(SUM(Sales[Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Ultima hasta la última ytD ventas completas - CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))

Ventas de Cumm: CALCULATE(SUM(Sales[Sales[SalesImporte]), filtro (fecha, fecha[fecha] <-maxx(date,date[date])))
Ventas de Cumm: CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))