Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi,
How is the data structured? Share data in a format that can be pasted in an Excel file.
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.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |