Calculations with date variables

[FiveAces]

I'm attempting to build a 12 month visualisation of actual sales based on the financial year and period selected in a slicer.

There is a date table (Date) with an active relationship to the fact table (Data).  If the date in the visual is outside the start date or end date it returns blank, otherwise it returns actual values.

When the start date and end date are coded manually the visual works perfectly. When the hardcoded dates are substituted for variables then the date range displayed is incorrect. I'm looking to correct this in DAX and not use relative date filters.

VAR startDate = DATE(2018, 8, 1)
VAR endDate = EOMONTH(DATE(2019, 8, 1), 0)
RETURN
IF(MAX('Date'[Date]) <= startDate, BLANK(),
    IF(MAX('Date'[Date]) >= endDate, BLANK(),
        [Actual]))

My problem occurs when trying to substitute the hardcoded dates with dates built from the selected values of the slicers (FY Year and FY Period from the Date calendar). The fiscal year end is 30th June so some calculations are performed to translate these into calendar dates.  To test the calculation, I have returned the variables startDate and endDate to a card to ensure they're outputting the same dates I'm looking for in this particular scenario - they do (01/08/2018 and 31/08/2019 respectively). However, when these variables are used in the calculation the result is incorrect. 

Actual 12 Months = 
VAR selectedYear = SELECTEDVALUE('Date'[FY Year])
VAR selectedPeriod = SELECTEDVALUE('Date'[FY Period])
VAR period = IF(selectedPeriod > 6, selectedPeriod - 6, selectedPeriod + 6) + 1
VAR startYear = INT(IF(selectedPeriod > 6, selectedYear - 1, selectedYear - 2))
VAR endYear = startYear + 1
VAR startDate = DATE(startYear, 8, 1)
VAR endDate = EOMONTH(DATE(endYear, 8, 1), 0)
RETURN
IF(MAX('Date'[Date]) <= startDate, BLANK(),
    IF(MAX('Date'[Date]) >= endDate, BLANK(),
        [Actual]))

It was my understanding that declaring a variable made it a constant but when I'm using the variables for 'year' in building the date it seems to break. As the variables are calculated before the RETURN I would be very interested to understand why this is happening. Any insights would be appreciated.

Thanks!

[v-diye-msft]

Hi @FiveAces 

I'm wondering what 's the definition in below formula?Do you refer to the current row? 

VAR selectedYear = SELECTEDVALUE('Date'[FY Year])
VAR selectedPeriod = SELECTEDVALUE('Date'[FY Period])

 And Do you have some sample data or preferably a sample pbix file.

[FiveAces · Accepted solution]

Hi @v-diye-msft,

While recreating a sample of this issue I realised that one of the slicers was not filtering the visual which was causing the issue.

I'll try and close/remove this (non) issue.

Thanks for your response.