Comparative time intellegence

Question

Hi everyone&nbsp;I hope someone has an easy answer to my question, because I have watched videos and tried formulas for a week and cannot find what I am looking for.&nbsp;In my Company we look specifically at total sales over different time periods compared to the previous year. The most common time periods we use is year to date compared to same period last year, month to date compared to same period last year, previous month compared to the same month last year, previous 3 months compared to the same three months last year, previous 6 months compared to the same months.&nbsp;So I used Totalytd and paralel period for the YTD numbers that worked fine. I was also able to do a 30 day running total compared to the same 30 days&nbsp; last year using the below measure.&nbsp;&nbsp;30 Day Moving Total = CALCULATE(SUM(Country_Sales[HLS]).FILTER(ALL('Date Table').'Date Table' [Date] &gt; MAX('Date Table'[Date]) - 30 &amp;&amp;'Date Table' [Date] &lt;= MAX('Date Table'[Date])))&nbsp;The 1 month, three month and six month calculations are supposed to calculate from the last full month of sales. So If we are in July the 1 month calculation need to cpmpare June this year to June last year. I got the previous month usingPrior month = CALCULATE([Total HLS].DATEADD('Date Table'[Date].-1 .MONTH)).&nbsp; I do not know how to sum three months using this measure.I have found measures that can create running totals from the last day of sales backward for days, months or years but not how sum the last 3 full month of data compared to the same period the previous year.&nbsp;&nbsp;The other issue is that all the data needs to be displayed in one table as below. SO I cannot use paralel period every time or use slicers (which would have been easier).&nbsp;&nbsp;&nbsp;CountryYTDSPLYYTD Variance6 Month TY6 Month PY6 month Variance3 Month TY3 Month PY1 Month TY1 Month PYSouth Africa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Botswana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I know there will be one calc I can use for all these parameters but I have no idea how to sum the previous three months and compare it to the same three months last year without acually inserting the dates in the measures or using slicers. This is a direct query&nbsp;from our SQL server and the report needs to refresh without me having to go and change measures or dates.&nbsp;

amitchandak · Accepted Answer

Peppie , for the standard period
Power BI — YTD Questions — Time Intelligence 1–5https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38aPower BI — QTD Questions — Time Intelligence 2–5https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839Power BI — MTD Questions — Time Intelligence 3–5 https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ePower BI — WTD Questions— Time Intelligence 4–5https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123Power BI — Day Intelligence Questions — Time Intelligence 5–5https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
&nbsp;
For rolling
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))
Rolling 7 days = CALCULATE(sum(Sal[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,Day))
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-13,MONTH))
&nbsp;
cumulative
Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(all(Table),Table[PRODUCT] &lt;=maxx(Table,Table[PRODUCT])))
Cumm Sales = CALCULATE(SUM(Table[Sales]),filter(all(Table),Table[PRODUCT] &lt;=max(Table[PRODUCT])))
&nbsp;
Standard formula
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

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 : https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184Appreciate your Kudos.
&nbsp;
&nbsp;

Forum Discussion

Comparative time intellegence

2 Replies

Related content

DAX for comparing time series data

Time intellegence an relative date slicer

Time vs Time Scatter Chart

Compare 2 Date Columns by Time Logic

Table Sort , sort order , comparer

Country	YTD	SPLY	YTD Variance	6 Month TY	6 Month PY	6 month Variance	3 Month TY	3 Month PY	1 Month TY	1 Month PY
South Africa
Botswana