Forum Discussion
Next year sales when year selected
hello all,
I have selected 2023 year in dashboard. when i try to look sum of sales for 2024 it shows blank.
is there any way that it will keep all filters but sum will retrive for next year.
I tried datesbetween but it doesnt work.
please help me on this.
Hi Narendra90 ,
In your case, it is suggested to create a separate year table for a slicer. When you select a year such as 2022, the table visual will show the data in 2023.
I made simple data for reference.
Below is the main table.
1.Create a separate year table. Here I created it by DAX.
Table 2 = DISTINCT(SELECTCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year",YEAR([Date])))Separate means no relationship between tables.
2.Then you can use the SELECTEDVALUE function to call the slicer's year.
NextYear = CALCULATE(SUM('Table'[Sale]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year])+1))As you can see, when I select 2022 in the slicer, the table visual and the card visual showed the data in 2023.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- 123abc
Community Champion
Try this formula:
NextYearSales =
VAR SelectedYear = YEAR(MAX('YourDateTable'[Date]))
RETURN
CALCULATE(
SUM('YourDateTable'[Sales]),
SAMEPERIODLASTYEAR('YourDateTable'[Date]),
YEAR('YourDateTable'[Date]) = SelectedYear + 1
)Replace 'YourDateTable' with the actual name of your date table.
This formula calculates the sum of sales for the next year based on the selected year from your dashboard filters.
Press Enter to create the measure.
Now, in your report or dashboard, you can use the "NextYearSales" measure to display the sum of sales for the next year while keeping other filters applied. When you select the year 2023 in your dashboard, it will calculate the sum of sales for 2024.
Remember to replace 'YourDateTable' with the actual name of your date table in your data model.
Hi Narendra90 ,
In your case, it is suggested to create a separate year table for a slicer. When you select a year such as 2022, the table visual will show the data in 2023.
I made simple data for reference.
Below is the main table.
1.Create a separate year table. Here I created it by DAX.
Table 2 = DISTINCT(SELECTCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year",YEAR([Date])))Separate means no relationship between tables.
2.Then you can use the SELECTEDVALUE function to call the slicer's year.
NextYear = CALCULATE(SUM('Table'[Sale]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year])+1))As you can see, when I select 2022 in the slicer, the table visual and the card visual showed the data in 2023.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
