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 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?
 
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.

4 Replies

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

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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'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.