Cumulative Calculation with Disconnected Date

DAX DAX Calculations
1

ContosoDisconnectedDates.pbix (18.4 MB)
I need to present a set of cumulative sales graphs on a dashboard and these need to play nicely with the other measures which are calculated based on the selected fiscal week. This being the case I’ve built a measure using a disconnected date table and it’s so nearly working - but my measure on the disconnected table ignores the lower and upper bounds I want to report on. I will attach the PBIX, the DAX for the measure is:

Sales YTD (Disconnected) =
VAR SelectedDate_ = MAX(‘Date’[Full Date])
VAR SelectedFY_ = MAX(‘Date’[Fiscal Year])
RETURN
CALCULATE (
[Sales £],
FILTER (
ALL ( ‘Date’ ),
‘Date’[Fiscal Year] = SELECTEDVALUE ( ‘Disconnected Date’[Fiscal Year] )
&& ‘Date’[Fiscal Week] <= SELECTEDVALUE ( ‘Disconnected Date’[Fiscal Week] )
&& SELECTEDVALUE(‘Disconnected Date’[Full Date]) <= SelectedDate_
&& SELECTEDVALUE(‘Disconnected Date’[Fiscal Year]) = SelectedFY_
)
)

It’s so nearly working… what am I doing wrong?

Cheers

2

@Jamie You are using column from Disconnected table in the Matrix, that’s opposite of what Disconnected table is to be used for, are you sure you do not want columns of the Date table in that Matrix?

This part of the code is not doing anything at all:

&& SELECTEDVALUE(‘Disconnected Date’[Full Date]) <= SelectedDate_
&& SELECTEDVALUE(‘Disconnected Date’[Fiscal Year]) = SelectedFY_

3

What I am trying to do is have a chart (currently the matrix) which will show the cumulative figures. And the other cards etc on the page will show the data for the currently selected week. So the date table is going to be filtered down to one week at all times. This being the case I wanted to use the disconnected one to handle all dates from the fiscal year that are prior to the selected value on Date.

4

I get that the disconnected date should be used for selection and the matrix/chart should be connected to the Date table - but in my use case I have maybe 200 measures involved in this dashboard page and I dont want to have to change the way they work just for the sake of a few line graphs… is there another way of achieving this?

5

Here’s what I did. Upon analysis the data the customer wanted a cumulative graph of was a small subset of the entire sales data and always will be. So I created a new fact table and joined that to the disconnected date. Works a treat.