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