Forum Discussion

Helper I

1 year ago

Solved

Row & Filter Context / DAX formula

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 Stateme...

Andrew-HLP
1 year ago
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.

Andrew-HLP

Helper I

1 year ago

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

Helper I

1 year ago

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
Super User
1 year ago
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
  Helper I
  1 year ago
  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.

Forum Discussion

Row & Filter Context / DAX formula

Related content

Filter on Visual Row Context

Remove visual filter context

If formula

DAX Formula Help

formula