dynamic last year value

[sarjensystems1]

Hii

I Have a requirement to display last year june month sales value for all the months data

ex june 2019 sales is 1000

then values for Jan 2020,feb 2020,mar 2020.....Dec 2020 should be 1000

june 2018 sales is 2000

then values for jan 2019,feb 2019,mar 2019.....dec 2019 should be 2000

and it should be dynamic

if june 2020 value is 3000

then for 2021 months it should be 3000

Thanks

[v-kelly-msft · Accepted solution]

Hi  @sarjensystems1 ,

Take the following steps:

1.Create a slicer table with all the years which equal to the table year +1,as you see below:

2.Create 2 calculated columns using below dax expressions to get the year and month from the table date:

Column = YEAR('Table'[Date])

Column 2 = MONTH('Table'[Date])

3.Create a measure to get the sales in June of last year.

Measure = CALCULATE(SUM('Table'[Consumption]),FILTER('Table','Table'[Column]=SELECTEDVALUE(Slicer[Year])-1&&'Table'[Column 2]=6))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

How is the data structured?  Share data in a format that can be pasted in an Excel file.

[mwegener]

Hi @sarjensystems1 ,

i'm not sure what the requirement for this is, but you could get the value in power query and create a corresponding data table.

Maybe you can give us some more information about the goal and provide some sample data.

[amitchandak]

@sarjensystems1 

Create a month slicer. Only month. And then display data across year using month and year.

You can Time intelligence and create last year mtd, last to last year mtd and show them with month year filter

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


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

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-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/