Forum Discussion
Helper IRow & Filter Context / DAX formula
Thank you for all your help 🙂
GOOD NEWS #1: With a bit of tweaking of the code you suggested I was able to (i) created a disconnected dates table, (ii) created measures to work with that table.
GOOD NEWS #2: After a lot of trial and error, I was able to limit the X-axis of my chart using the disconnected dates table. The soltion may not be the neatest in the world, but it works!
HERE IS THE DETAILED IN CASE IT HELPS ANYONE ELSE...
(i) I made an exact copy of my dates table [Fiscal Calendar] and called it [Fiscal Calendar (Disconnected)]. I have a 31st March year end, so it was easier to replicate the table then build a slimmed down version and it gives me the flexibility to use any of the columns.
(ii) I then created two new measures which work in conjunction with [Fiscal Calendar (Disconnected)]. The measure only returns a value if the date from the disconnected calandar (i.e. the date on the x-axis of my chart) is within the required 13 month range, otherwise it returns blank().
GL Value Period New (13 Months) =
var _ldmend = [Selected Date]
var _ldmstart = EOMONTH([Selected Date],-13)+1
var _ld13m = EOMONTH(lastdate('Fiscal Calendar (Disconnected)'[Date]),0)
RETURN
if ( _ld13m >= _ldmstart && _ld13m <= _ldmend ,
CALCULATE(
[GL Value Period],
'Fiscal Calendar'[Date] = _ld13m
),
blank()
)
GL Value Run Tot New (13 Months) =
var _ldmend = [Selected Date]
var _ldmstart = EOMONTH([Selected Date],-13)+1
var _ld13m = EOMONTH(lastdate('Fiscal Calendar (Disconnected)'[Date]),0)
RETURN
if ( _ld13m >= _ldmstart && _ld13m <= _ldmend ,
CALCULATE(
[GL Value Run Tot],
'Fiscal Calendar'[Date] = _ld13m
),
blank()
)
(iii) The nested [Selected Date] measure converts the selected month and fiscal year (from my slicers) to a date.
This provides a nice clean solution and avoids referencing the filtered [Fiscal Calendar] table which cauises unexpected interactions with other measures). I could have written code to do the conversion, but as the information is already stored in the dates table, it seemed less error prone to simply look up the relevant date.
Selected Date =
VAR _SelectedFiscalMonthName = max('Lookup Month'[Month])
VAR _SelectedFiscalYear = max('Lookup Year'[FiscalYear])
RETURN
calculate(
max('Fiscal Calendar (Disconnected)'[EOMDate]),
all('Fiscal Calendar (Disconnected)'),
'Fiscal Calendar (Disconnected)'[FiscalMonthName]=_SelectedFiscalMonthName
&& 'Fiscal Calendar (Disconnected)'[FiscalYear]=_SelectedFiscalYear
)
NB: As we have a 31st March year end, Sept 2024-25 converts to 30/09/2024 wheras Mar 2024-25 converts to 31/03/2025.
SET-UP OF VISUAL / OUTPUT
NOTE RE. 13 MONTH WINDOW
I didn't use a calculated table like the [13MonthWindow] table as you suggested as calculate tables are only "recalculated if any of the tables they pull data from are refreshed or updated" so they can't be dynically updated by a slicer (see https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables) for more information about this).
I hope this helps others who may be facing the same issue.
Super UserHi Andrew-HLP ,
You're encountering an issue with the [GL Value Run Tot] measure because the slicers on 'Lookup Month' and 'Lookup Year' are indirectly filtering the 'Fiscal Calendar' table, which in turn restricts the rows visible during evaluation. In scenario 4a, when the slicers are applied, only data for Jan 2025 is visible to the DAX engine. As a result, your DATESBETWEEN logic is unable to compute a running total properly for earlier months, since those rows are filtered out. This is why the measure returns an accurate total only for the selected month and either blanks or incorrect results for all other periods. In scenario 4b, you've removed the interaction between the slicers and the matrix visual (either through editing interactions or by modifying the DAX formula), which allows the full date range to remain visible and enables the running total to calculate correctly for each month row.
To fix this and make the measure behave correctly even when slicers are applied, you can use REMOVEFILTERS on the slicer tables ('Lookup Month' and 'Lookup Year') inside the measure. This ensures that the DATESBETWEEN logic can still see all dates in the 'Fiscal Calendar' table while the visual context (like rows in a matrix) continues to drive the monthly breakdown. Here's the revised version of your measure:
GL Value Run Tot =
CALCULATE(
[GL Value Period],
DATESBETWEEN(
'Fiscal Calendar'[Date],
[First Date of Model],
[Last Date of Month]
),
REMOVEFILTERS('Lookup Month'),
REMOVEFILTERS('Lookup Year')
)
If necessary, you can also consider using REMOVEFILTERS('Fiscal Calendar') followed by reapplying the date range filter explicitly via DATESBETWEEN, although in your case, targeting the slicer tables should be sufficient since your calendar table drives the rest of the model.
Regarding your second question, you’re almost there with the 13-month restriction logic. You correctly defined _Start and _End based on the current selected month. The reason the visual may still not be showing exactly 13 months is likely because the slicers or filter context are still limiting which dates are visible on the axis. If you're using 'Fiscal Calendar'[Date] on the X-axis, and the slicer is restricting it, your measure will work but the visual might not show the intended range. One solution is to use a disconnected date table that defines the last 13 months and use that table for the X-axis, independent of the slicers. Another approach is to add a calculated column in the 'Fiscal Calendar' table that flags dates within the 13-month window and use that column as a filter in the visual.
Your measure for 13 months of data using the existing calendar table can be written like this:
13 Months Actual =
VAR _Start = EOMONTH(MAX('Fiscal Calendar'[Date]), -13) + 1
VAR _End = EOMONTH(MAX('Fiscal Calendar'[Date]), 0)
RETURN
CALCULATE(
[GL Value Run Tot],
REMOVEFILTERS('Lookup Month'),
REMOVEFILTERS('Lookup Year'),
'Fiscal Calendar'[Date] >= _Start,
'Fiscal Calendar'[Date] <= _End
)
This ensures that the running total is correctly calculated across all dates in the 13-month range, regardless of slicer selection. If the X-axis is still not displaying all desired months, consider adding a visual-level filter based on a calculated column that flags whether a date falls within this 13-month window.
Best regards,
Andrew-HLPFirst of all a huge thank you for your reply. Unfortunately this doesnt work and the new measure, [GL Value Run Tot New] returns the same spurious results. What I wuld really like is a measure which only returns a value for the period selected on the slicer.
Thanks in advance 🙂
- Andrew-HLP
UPdate: I have managed to solve question 1 by changing the DAX expression for [GL Value Run Tot] as follows:
GL Value Run Tot New =
var _ldm = [Last Date of Month]RETURNCALCULATE([GL Value Period],'Fiscal Calendar'[Date]<= _ldm)Still working on Question 2 🙂
- DataNinja777
Hi Andrew-HLP ,
Nice work solving Question 1 — your updated measure using a variable for the last date of the month and directly filtering the 'Fiscal Calendar'[Date] <= _ldm condition inside CALCULATE is clean and effective. That approach sidesteps the issue of slicer interaction by leveraging row context in a smart way.
Next for Question 2, using a disconnected table is a clean approach, and here’s a step-by-step guide to setting up a disconnected calendar table to show a fixed 13-month window for your running total visual, without it being affected by slicers on 'Fiscal Calendar'.
To implement the disconnected calendar table approach, you can start by creating a new table that contains the last 13 months of data, independent of the main 'Fiscal Calendar' table. This table will serve as your X-axis in visuals and won’t be affected by slicers or filters applied to the main calendar. You can create it with the following DAX:13MonthWindow = VAR _End = EOMONTH(TODAY(), 0) VAR _Start = EOMONTH(_End, -12) + 1 RETURN ADDCOLUMNS( CALENDAR(_Start, _End), "YearMonth", FORMAT([Date], "yyyy-mm"), "FiscalYearPeriod", FORMAT([Date], "yyyy") & "-P" & FORMAT([Date], "mm") )This creates a simple calendar table from 13 months ago through the end of the current month, along with helpful columns for formatting and labeling. You can then use '13MonthWindow'[Date] or '13MonthWindow'[FiscalYearPeriod] as your X-axis in visuals.
Next, update your running total measure so it uses this disconnected table for context. The idea is to use the date from '13MonthWindow' as a reference point and calculate the cumulative total in 'Fiscal Calendar' up to that date. Here's the DAX for that:
GL Value Run Tot (13M) = VAR _CurrentDate = MAX('13MonthWindow'[Date]) RETURN CALCULATE( [GL Value Period], FILTER( ALL('Fiscal Calendar'), 'Fiscal Calendar'[Date] <= _CurrentDate ) )This ensures the running total is always calculated based on the row context from the disconnected date table. The use of ALL('Fiscal Calendar') clears any filters on the main calendar so the running total isn't restricted by slicers or visuals that might otherwise limit it.
If you’re using '13MonthWindow'[FiscalYearPeriod] as the X-axis, it’s helpful to create a sort column to ensure correct chronological order. You can do this by adding a numeric column like this:
13MonthWindow = VAR _End = EOMONTH(TODAY(), 0) VAR _Start = EOMONTH(_End, -12) + 1 RETURN ADDCOLUMNS( CALENDAR(_Start, _End), "YearMonth", FORMAT([Date], "yyyy-mm"), "FiscalYearPeriod", FORMAT([Date], "yyyy") & "-P" & FORMAT([Date], "mm"), "SortOrder", YEAR([Date]) * 100 + MONTH([Date]) )Now "SortOrder" is a proper column name in DAX, and the expression calculates a sortable numeric value like 202401, 202402, etc. You can then use this column to sort 'FiscalYearPeriod' by 'SortOrder' in the model view to ensure the months appear in the correct order on your visual.
Best regards,