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