Time Intelligence Measures with Dynamic Calendar Table

[Anonymous]

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 working properly. I have a feeling that this has to do with the fact that I use a dynamic calendar table that always goes out to the end of the year and does not stop with today's date. My previous calendar table stopped on today's date and my measures worked properly, but stopped when I chaged my Calendar to always go till end of the year.

I am using the following measures:

Current MTD = TOTALMTD(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),Calendar[Date])

Current QTD = TOTALQTD(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),Calendar[Date])

Current YTD = TOTALYTD(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),Calendar[Date])

Same Month Last Year = CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),PARALLELPERIOD('Calendar'[Date],-12,MONTH))

Same MTD Last Year =  CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])))

Same Quarter Last Year = CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),PREVIOUSQUARTER('Calendar'[Date]))

Same QTD Last Year = CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),DATEADD(DATESQTD(Calendar[Date])-1,YEAR))

Last Year = CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),PREVIOUSYEAR('Calendar'[Date]))

Last YTD = CALCULATE(SUM('Table1'[Revenue])+SUM('Table2'[Revenue]),DATEADD(DATESYTD(Calendar[Date])-1,YEAR))

Past 30 Days = CALCULATE([Total Revenue],CALCULATETABLE('Calendar',FILTER('Calendar,[Past 30]=TRUE())))

Past 60 Days = CALCULATE([Total Revenue],CALCULATETABLE('Calendar',FILTER('Calendar,[Past 60]=TRUE())))

Past 90 Days = CALCULATE([Total Revenue],CALCULATETABLE('Calendar',FILTER('Calendar,[Past 90]=TRUE())))

Past 120 Days = CALCULATE([Total Revenue],CALCULATETABLE('Calendar',FILTER('Calendar,[Past 120]=TRUE())))

Calendar Measures mentioned above, w/ 30/60/90/120 respectively:

Past 30 = VAR EndDate = EOMONTH(TODAY(),-1) VAR StartDate = EDATE(EOMONTH(TODAY(),-1),-1)+1 RETURN IF(MAX('Calendar'[Date])>=StartDate && Max('Calendar'[Date])<=EndDate,True(),False())

Here is what the data should be:

But here is wht I am getting:

And here is a picture of my calendar table if it helps:

What am I doing wrong??? Thank you!

[amitchandak · Accepted solution]

@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))))

[Anonymous]

Excellent! Thank you so much, @amitchandak for all of the helpful information! On top of the help with measures, another thing you really helped with was reminding me to add a date filter so the max date ended with today. I very much appreciate it!

[Greg_Deckler]

Sample data as text always helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...