Filtering data on graph to current and previous year, according to date slicer.

[DanSanDST]

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

 And 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?

[v-pgoloju]

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

[v-pgoloju]

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

[DanSanDST · Accepted solution]

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.

[DanSanDST]

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.

[Ashish_Mathur]

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.

[DanSanDST]

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.

[Ashish_Mathur]

PBI file attached.

[DanSanDST]

So in the end it wasnt that complicated, i just needed to get the max date from a different table so it becomes a static value, since doing it on the same table re evaluates at every row calculation, i modified my original formula with that and it works without much changes. Now i finally learned my leason on why auxiliar date tables are good.

sum curr/prev saidi= 
var _date = max(dates[Date])
return
calculate(SUM(Table1[SAIDI]), FILTER(table1, Table1[Year]>=year(_date)-1 && Table1[Year]<=year(_date)) )

[v-pgoloju]

Hi @DanSanDST,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Ashish_Mathur  for their prompt and helpful responses.

Just following up to see if the solutions 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

[DanSanDST]

thanks, i will test with my data, since my idea is having a single slicer at the beginning and multiple measures, one is this, other can be monthly values last 5 years, etc. I was thinking of using a date table (like people always recommends) but i kind of dont like using them. I guess it recalculates when data is refreshed.

[DanSanDST]

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.