Forum Discussion
Helper IFiltering data on graph to current and previous year, according to date slicer.
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.
Helper Ii 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.
Super UserPBI file attached.
- 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
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)) )
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