Forum Discussion
Helper IIIdynamic last year value
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
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,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
- amitchandak
Super User
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/ - mwegener
Most Valuable Professional
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.
- Ashish_Mathur
Hi,
How is the data structured? Share data in a format that can be pasted in an Excel file.
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
