Forum Discussion
Filtering data on graph to current and previous year, according to date slicer.
Greetings, im trying to do something i already figured out a year ago, but reproducing it and using the same DAX formula now gives a different result.
First i limit the max date to read data with a simple slicer. If i use the measure shown in the image for the Y axis, and filtering Year by a hardcoded number, it works, chaning year to 2025 shows no data, since the slicer is set to a 2024 date.
Now if i instead change the Year value to something calculated/formula it ends up showing every year (and if i put a -1 for that year it shows nothing)
The DAX formula i had before to filter between year selected in slicer and previous was:
MEASURE = var _date = MAX(Table[DATE])
return
CALCULATE(COUNTROWS(Table), FILTER(Table, Table[Year]<=YEAR(_date) && Table[Year]>=YEAR(_date)-1)) +0And i was sure it worked, since i tested with with data for 3 years and it showed only the current and previous like i wanted.
Any suggestions to achieve this?
Welp i though i solved it, since my previous reasoning, i figured i needed to calculate the max date and year every time while using all the data and not just current row:
sum curr/prev saidi emp = var _fech = CALCULATE(max(Table1[fh_inicio_interrupcion]),ALL(Table1[Year])) return calculate(SUM(Table1[SAIDI empresa]), FILTER(table1, Table1[Year]>=YEAR(_fech)))this returns:
wich seems odd that it takes the values from previous year up until the selected month?? At first i had "ALL(Table1)" inside the calculate in "var _fech", wich would give me always the max date on the data, so if i slice my dates UNTIL 2023, it wouldnt show any data since it will always calculate the max year in all current data.
11 Replies
Also using Filter(Table1, Table1[year]>=year(today() ) gives a single year result in the graph, but using the max date on the range gives the whole range instead.
Whats worse is that the measure is calculating the correct value, i did some tests and if use the function to calculate last 3 years, it gives the correct value, but in the graph it shows all the data.
Hi,
I am not clear about your question. Share some data to work with and show the expected result in a simple table format. Share data in a format that can be pasted in an MS Excel file.
i showed both the expected result and current, here is some simple data:
date year value 01-01-2022 2022 1 01-05-2023 2023 1 01-06-2023 2023 1 01-07-2023 2023 1 01-01-2024 2024 1 01-02-2024 2024 1 01-05-2025 2025 1 01-07-2025 2025 1 the idea is showing in a bar graph only values for 2024 and 2025, calculating the MAX date (that can be sliced on the page to set a new max date).
Should be: (works using a direct value "2024" on the filter)
what actually happens: (calculating the max date and take its year-1, wich also returns 2024. the graph shows the data from all the years, even tho the measure calculates the correct total.
PBI file attached.
My problem is that i have a global slicer for the max date from all the data i want to show, then i want to calculate and show only data from that date's year and previous, but it will re calculate at every row so now the year in the current row is the max date and year, so i end up with all the data on the graph. If you use a fixed value as a comparison it will do it properly since that wont change, but you cant set global variables or anything similar to achieve this.
My final goal is a monthly report that can be evaluated at any date that you pick (a slicer), so it needs to calculate the current and previous year from that date (i haven have a graph with "from last 5 years"). Im sure i already did it before, but now its behaving differently.
Welp i though i solved it, since my previous reasoning, i figured i needed to calculate the max date and year every time while using all the data and not just current row:
sum curr/prev saidi emp = var _fech = CALCULATE(max(Table1[fh_inicio_interrupcion]),ALL(Table1[Year])) return calculate(SUM(Table1[SAIDI empresa]), FILTER(table1, Table1[Year]>=YEAR(_fech)))this returns:
wich seems odd that it takes the values from previous year up until the selected month?? At first i had "ALL(Table1)" inside the calculate in "var _fech", wich would give me always the max date on the data, so if i slice my dates UNTIL 2023, it wouldnt show any data since it will always calculate the max year in all current data.
Hi DanSanDST,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi DanSanDST,
Just following up to see if the responses provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar
