Time Intelligence Functions

Question

Hi Guru's,&nbsp;Hope you are doing good. I need your help in figuring out the formula for couple of new metrics.&nbsp; I am new to Power BI and this is my 3rd day playing with the tool. I have created "Current Year YTD Sales" and "Prior Year YTD Sales" by looking at few blogs and discussion forum.&nbsp; Now, the other requirement is to create "Prior Year Next Month Sales" which is Aug 2019 and&nbsp;"Prior Year&nbsp; Next 3 Month Sales" which is a total sales of Aug 2019, Sep 2019 and Oct 2019. Could you please help me.Thanks, Sandeep

pragati11 · Answer

HI&nbsp;Anonymous&nbsp;,&nbsp;Can you share some sample data please?https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490&nbsp;Thanks,Pragati&nbsp;

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
I attached the pbix file with the version of 2020 July. Today is 8/4/2020. Here is the result.

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak · Answer

Anonymous , with time intelligence and date table
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"))
&nbsp;
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 year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
&nbsp;
last year MTD Sales next month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-11,MONTH)))
last year MTD Sales next to next month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-10,MONTH)))
&nbsp;
same way you can use rolling
Rolling 3 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-9,month)),-3,MONTH))
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))
&nbsp;
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#M184
&nbsp;
&nbsp;
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-64b0b4a4090eAppreciate your Kudos.

anonymous · Answer

Hi Pragathi,&nbsp;&nbsp;Thanks for your reply.&nbsp;our issue is similar to the below post.I have followed this post&nbsp;https://community.powerbi.com/t5/Desktop/Current-Month-Previous-Month-and-Before-Previous-Month/td-p/462565&nbsp;&nbsp;and ended up creating the Measures but column is showing blank data.&nbsp;&nbsp;&nbsp;&nbsp;Thanks,Sandeep&nbsp;

anonymous · Answer

Pragati11&nbsp;...Please see the attached correct picture&nbsp;

Forum Discussion

Related content

Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

Interesting effects with calendar-based time intelligence

Real-Time Datasources for Real-Time Intelligence.

Issue with SAMEPERIODLASTYEAR and Manual Time Intelligence

Real Time Intelligence - Dashboard Refresh Rate