DAX Measure Returns (BLANK)

[Taxidea_Taxus]

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
    )
)

[Greg_Deckler · Accepted solution]

@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 · Accepted solution]

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]

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.

[FreemanZ]

hi @Taxidea_Taxus ,

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

[Taxidea_Taxus]

• Relative date is set to current year permanently via Filters pane
• Extended price is set to >= $1 permanently via Filters pane

[Taxidea_Taxus]

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

[Greg_Deckler · Accepted solution]

@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]

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]

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.