Forum Discussion

Taxidea_Taxus's avatar
Taxidea_Taxus
Advocate 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...
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
)
Greg_Deckler's avatar
Greg_Deckler
Community 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
Advocate 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().