Row & Filter Context / DAX formula

[Andrew-HLP]

As a relative newbie to DAX, I am struggling with row & filter context. 

BACKGROUND

I am building a financial dashboard.  The fact table has data for each account in the  P&L / Income Statement (sales, cost of sales, overheads etc) and for each  account in the balance sheet (cash, debtors, creditors, equity etc).

For P&L accounts reports need show either the value for the month (e.g. Jan 2025) or the value for quarter to date or year to date.  I can achieve this using standard time intelligence functions in my measures (TOTALMTD, TOTALQTD,TOTALYTD, PREVIOUSMONTH, NEXTMONTH, SAMEPERIODLASTYEAR etc).  

For balance sheet accounts I need to calculate the  running sum from the start of time up to the end of the current month (e.g. 1/4/2000 - 28/1/2025).  I use the DATESBETWEEN function to do this (I cant use the column function of the same name as it only works on data displayed in the table).

I also need to be able to restrict my graphs, bar charts etc to x periods of date (e.g. 13 months from Jan 2024 to Jan 2025).

TABLES

My table structure looks like this

'Lookup Month' and 'Lookup Year' are used to filter the main date table ('Fiscal Calendar').  This in turn filters the fact table ('GL Data').

QUESTION 1:

My two base level measures are:

[GL Value Period]   which is used to return the value for the period

[GL Value Run Tot]   which is used to return a running total for the balance sheet

With the slicer values set to Jan 2024-25 I get the following values:

Scenarios 1,2 and 3 return the expected values.  Scenario 4 does not. In scenario 4a, when the data is filtered using the slicer a spurious value is returned for every time period other than thr time period set by the slicers.  In scenario 4b, when the filter is removed (by editing the interactions on the page or in teh DAX formula, I get the correct result in every month.  The desired behavior when the slicers are set is per scenario 2.  What am I doing wrong?

GL Value Period = calculate (sum('GL Data'[Value]))

GL Value Run Tot = CALCULATE( [GL Value Period] ,

DATESBETWEEN('Fiscal Calendar'[date] , [First Date of Model] , [Last Date of Month])  )

First Date of Model = firstdate(all('fiscal calendar'[date]))

Last Date of Month = EOMONTH(MAX('Fiscal Calendar'[Date]),-0)

 

 QUESTION 2: 

How can I restrict my graphs, bar charts etc to x periods of date (e.g. 13 months from Jan 2024 to Jan 2025).  I have been succesfully able to do this for P&L accounts by adding the date to to X axis and a measure which returns 13 months data to the Y axis, but this does not work for balance sheet accounts - although this is most likely related to the above issue, so once I fix that issue, I may have fixed this.

var _Start = EOMONTH(MAX('Fiscal Calendar'[Date]),-13) +1 // Use >=

var _End = EOMONTH(MAX('Fiscal Calendar'[Date]),-0) // Use <=

RETURN

13 Months Actual =

CALCULATE (

    [GL Value Run Tot],

    all('Lookup Month'), all('Lookup Year'),

    'Fiscal Calendar'[Date] > _Start,

    'Fiscal Calendar'[Date] <= _End

)

[DataNinja777]

Hi @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-HLP]

First 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]

RETURN

CALCULATE(

    [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,

[Andrew-HLP · Accepted solution]

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.