Measure with VAR not working as expected

[Mark1982]

First report using Fiscal year logic. For everything there is a first time. Have decided on pattern and approach. Generated a key column in my date table using RANKX which is use a filtering when for instance comparing this against past quarter. Seems to be a viable approach and pattern but my measures are failing :(.

--- for instance measure

Sales Amount PQ =

VAR __MeasureDate = LASTDATE('Date'[Date]) // Fiscal quarter selected in slicer

VAR __MeasuredFiscalQr = CALCULATE(MIN('Date'[FiscalQuarterKey]),FILTER('Date','Date'[Date] = __MeasureDate)) // Get FiscalQuarterKey beloging to measured date

VAR __PastFiscalQr = __MeasuredFiscalQr - 1 // FiscalQuarterKey - 1 is past quarter

//VAR __PastFiscalQr = 3 // Hardcoded value

VAR __Amount = CALCULATE(SUM(GeneralSales[Amount]), USERELATIONSHIP(GeneralSales[Date],'Date'[Date]), FILTER('Date','Date'[FiscalQuarterKey] = __PastFiscalQr))

RETURN ( __Amount)

Measure results blank when __PastFiscalQr = __MeasuredFiscalQr - 1 but works when using the hardcoded __PastFiscalQr = 3

Tried declaring INT and VALUE without luck. Anyone some suggestions?

https://1drv.ms/u/s!Ag3hOdYVKV71qkVw9go35YozMPbW 

[Mark1982]

Maybe good to mention is that the Date'[FiscalQuarterKey] column is data type whole number / integer and that every date record has a value from 1 to 16.

[Greg_Deckler]

@Mark1982 Few things. First I would be a tad leary of LASTDATE since it is a TI function and designed to work with Gregorian calendars. Shouldn't be an issue but maybe must use MAX instead. Might need an ALL in your Amount calculation but the hard coding says no maybe althought might be because of context. Essentially, if in context you are getting your "current" fiscal quarter then that would tend to indicate that your last fiscal quarter is not in context so you are trying to grab rows with a fiscal quarter of 3 when only fiscal quarters of 4 are in context.

Try returning each of your variables in turn in your RETURN statement and make sure they are returning what is expected in context.

Other than that, difficult to say without more information. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Mark1982]

Thanks so far. I believe its a context issue. Have shared a PBIX file.

[v-kkf-msft · Accepted solution]

Hi @Mark1982 ,

I think you need to use ALL to removes the filter on 'Date'[FiscalQuarterKey] column, try the following measure:

Sales Amount PQ = 

VAR __MeasureDate = LASTDATE('Date'[Date]) // Fiscal quarter selected in slicer
VAR __MeasuredFiscalQr = CALCULATE(MIN('Date'[FiscalQuarterKey]),FILTER('Date','Date'[Date] = __MeasureDate)) // Get FiscalQuarterKey beloging to measured date
VAR __PastFiscalQr = __MeasuredFiscalQr - 1 // FiscalQuarterKey - 1 is past quarter
//VAR __PastFiscalQr = 3 // Hardcoded value
 
VAR __Amount = CALCULATE(SUM(GeneralSales[Amount]), USERELATIONSHIP(GeneralSales[Date],'Date'[Date]), FILTER(ALL('Date'),'Date'[FiscalQuarterKey] = __PastFiscalQr))

RETURN __Amount

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.