Creating a Cumulative Sales Range Starting Fiscal YTD to Beginning Date Filter

DAX DAX Calculations
1

I know the heading might sound a little confusing, so I will elaborate.

Please look at the PBI query.

Data - Date Range outside a slicer.xlsx (21.1 KB)
FYTD Query.pbix (161.2 KB)

I’m trying to create a measure that will give me the cumulative sales from certain point of time based on a date slicer.

I have included a number of tables to clarify my goal in the calculation.

I am trying to create cumulative sales periods.

  1. Sales period stating fiscal year ending at the start date of the date slicer.
  2. Sales period starting at start date of slicer to the end date on the slicer.

For instance my fiscal year ends 9/30. I have my date slicer set from 2/14/21 - 2/28/21.

The first calculation which is the one I am having difficulty with creating would begin 10/1/20 and go through 2/13/21.

Second calculation which I think is just a SUM( sales ) would go from 2/14/21 - 2/28/21.

I have tried numerous measures, but I don’t think any of them are correct.

Thank you,

1 Like
2

@ibesmond ,

Nice job explaining the problem and providing all the relevant information. That definitely makes it easier for us to provide quality support and solutions.

See how this works for you:

image

The key is building the measures off the date slicer using a disconnected date table, rather than the primary date table within the model.

The HarvestMin and HarvestMax measures are just the standard date slicer harvest pattern. Calculating the beginning of the fiscal year based on HarvestMin is a little more complex:

Harvest Start of  FY Date =
VAR _SelDate = [Harvest MinDate]
VAR _SelFY =
    LOOKUPVALUE( 'Date'[Fiscal Year], 'Date'[Date], _SelDate )
VAR _Result =
    CALCULATE(
        MIN( 'Date'[Date] ),
        FILTER(
            ALL( 'Date'[Date], 'Date'[Fiscal Year] ),
            'Date'[Fiscal Year] = _SelFY
        )
    )
RETURN
    _Result

Then from there, it’s just calculating total sales over the two different date ranges defined by the slicer:

Total Sales Pre-Slicer = 
CALCULATE(
    [Total Sales],
    FILTER(
        ALL( 'Date'[Date] ),
        'Date'[Date] >= [Harvest Start of  FY Date]
            && 'Date'[Date] < [Harvest MinDate]
    )
)

Total Sales Within Slicer = 
CALCULATE(
    [Total Sales],
    FILTER(
        ALL( 'Date'[Date] ),
        'Date'[Date] >= [Harvest MinDate]
            && 'Date'[Date] <= [Harvest MaxDate]
    )
)

I hope this is helpful. Full solution file attached below.

1 Like
3

Hi @ibesmond, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

@BrianJ
Looks to be working in my model. Still in the validation stage, but so far ties out perfectly. Thank you for your response. The disconnected date table was a challenge and forced me to have to modify all my current measures to get them to react to the disconnected table.

I had to create an inactive relationship between the disconnected date table and my original date table and then utilize the USERELATIONSHIP function to align my measures with the disconnected date slicer when it was appropriate. Now I feel like I am an expert when it comes to the function, which is all thanks to your support answering my question. Thanks again! :+1:

5

@ibesmond ,

Thanks very much for the update. Glad to hear that’s working well for you.

– Brian