Need n, n-1, n-2, n-3, n-4, n-5 months data

nprasanthk — Wed, 10 May 2023 05:31:18 GMT

Hi All,

I am struggling to achive the below requirment.

requirment is:

we have two dates 1. Forecast dates 2. forecast months.

each forecast dates have 12 forecast months. currently i have only 5 forecast dates. (01-Aug-22, 10-Sept-22, 20-Oct-22, 25-Nov-22, 30-Dec-22)

if user select the 23-Feb forcast month then that Feb month is current month this is (n)

then 23-Jan Month Feb becomes (n-1)

then 22-Dec month Feb becomes (n-2)

then 22-Nov Month Feb becomes (n-3)

then 22-Oct Month Feb becomes (n-4)

like if user selec 22-Dec then it becomes n then rest all n-1, n-2, n-3, n-4.

so it should be dynamic.

my measure is SUM(Quantity)

Thanks in Advance.

Re: Need n, n-1, n-2, n-3, n-4, n-5 months data

amitchandak — Wed, 10 May 2023 05:42:56 GMT

nprasanthk , with help from date table( if needed create date from month year) joined with date of your table

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

last 2nd MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
last 2nd month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

last 3rd MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
last 3rd month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-2,MONTH)))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

If you need a trend (of 6 months when 1 is selected) then you consider the independent table

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

topic Need n, n-1, n-2, n-3, n-4, n-5 months data in DAX Commands and Tips

Need n, n-1, n-2, n-3, n-4, n-5 months data

Re: Need n, n-1, n-2, n-3, n-4, n-5 months data