Forum Discussion

Taxidea_Taxus's avatar
Taxidea_Taxus
Icon for Advocate I rankAdvocate I
2 years ago
Solved

DAX Measure Returns (BLANK)

I have created a DAX measure to sum total sales for the previous calendar year. It needs to respect all active filters. When I run my measure it returns (BLANK). I would appreciate any suggestions on how to resolve this issue.

 

_LYSales = 
VAR CurrentYear = YEAR(MAX('invoicehistory'[invoice date]))
RETURN
CALCULATE(
    SUM('invoicehistory'[extended price]),
    FILTER(
        ALLSELECTED('invoicehistory'),
        YEAR('invoicehistory'[invoice date]) = CurrentYear - 1
    )
)
DAX
measure
power bi
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Taxidea_Taxus Try:

    _LYSales = 
VAR CurrentYear = YEAR(MAX('invoicehistory'[invoice date]))
RETURN
    SUMX(
      FILTER(
        ALLSELECTED('invoicehistory'),
        YEAR('invoicehistory'[invoice date]) = CurrentYear - 1
      ),
      [extended price]
    )
  • Taxidea_Taxus's avatar
    Taxidea_Taxus
    2 years ago

    I found the following code resolved my measure issue:

     

    _LYSales = 
VAR CurrentYear = YEAR(MAX('invoicehistory'[invoice date]))
RETURN
CALCULATE(
    SUM('invoicehistory'[extended price]),
    REMOVEFILTERS('InvoiceHistory'[Invoice Date]),
    YEAR('invoicehistory'[invoice date]) = CurrentYear - 1
)

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Taxidea_Taxus Try:

    _LYSales = 
VAR CurrentYear = YEAR(MAX('invoicehistory'[invoice date]))
RETURN
    SUMX(
      FILTER(
        ALLSELECTED('invoicehistory'),
        YEAR('invoicehistory'[invoice date]) = CurrentYear - 1
      ),
      [extended price]
    )
    • Taxidea_Taxus's avatar
      Taxidea_Taxus
      Icon for Advocate I rankAdvocate I
      2 years ago

      Hello Greg_Deckler , thank you for the resources you directed my to earlier. I have created a .vpax file of this report. You can view it by clicking here. All measures in this report work as intended with one exception...the _LY Sales. It returns the correct answer but does not respect existing filters as the other measures do. No matter what filter options are used, this filter's output remails the same. 

    • Taxidea_Taxus's avatar
      Taxidea_Taxus
      Icon for Advocate I rankAdvocate I
      2 years ago

      Thank you for your suggestion. I believe we're getting closer to the solution. The value for this measure changes with each filter selection. Two filter selections return Blank() with the last filter section returning a value. I am wondering if there is a way to return a currency value vs. Blank(). 

       

       

  • Taxidea_Taxus's avatar
    Taxidea_Taxus
    Icon for Advocate I rankAdvocate I
    2 years ago

    Thank you for your prompt response to my post! I have tried your suggestion and am reviewing the results. There are three different filter scenarios. Two return (BLANK) and one returns data. I looked at the data (SQL table) and all three scenarios should return data. I am suspicious of this being a date-related issue in the measure. Any further suggestions would be most appreciated. 🙂

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi Taxidea_Taxus ,

     

    what filter context do you have for the expected measure? or with which columns are you plotting the measure?

    • Taxidea_Taxus's avatar
      Taxidea_Taxus
      Icon for Advocate I rankAdvocate I
      2 years ago
      • Relative date is set to current year permanently via Filters pane
      • Extended price is set to >= $1 permanently via Filters pane
  • Taxidea_Taxus's avatar
    Taxidea_Taxus
    Icon for Advocate I rankAdvocate I
    2 years ago

    I found the following code resolved my measure issue:

     

    _LYSales = 
VAR CurrentYear = YEAR(MAX('invoicehistory'[invoice date]))
RETURN
CALCULATE(
    SUM('invoicehistory'[extended price]),
    REMOVEFILTERS('InvoiceHistory'[Invoice Date]),
    YEAR('invoicehistory'[invoice date]) = CurrentYear - 1
)
    • Taxidea_Taxus's avatar
      Taxidea_Taxus
      Icon for Advocate I rankAdvocate I
      2 years ago

      In this corrected version, REMOVEFILTERS('InvoiceHistory'[Invoice Date]) will remove any existing filters on the Invoice Date column before the calculation is performed. The filter YEAR('invoicehistory'[invoice date]) = CurrentYear - 1 is then applied to calculate the sum of extended price for the previous year.