Forum Discussion

Mark1982's avatar
Mark1982
Icon for Helper I rankHelper I
5 years ago
Solved

Measure with VAR not working as expected

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...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

Mark1982's avatar
Mark1982
Icon for Helper I rankHelper I
5 years ago

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

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

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.