When using max date on date table but the max date is set to end of year

[akhaliq7]

I have a date table whereby I set the date range to the end of year as is recommended. But when I need to use that field to filter a measure (as is recommended instead of filtering with a column from a fact table) I need to get the value that is max date in the fact table as the max date from the date table will return nothing in the fact table.

e.g.

Sales table date range 01/01/2023 - 05/04/2023

Date table date range 01/01/2023 - 31/12/2023

------------------------------------------

measure:

Sales Last Month =

CALCULATE(

    SUM('Sales'[Profit]),

    'Date'[Date] = MAX('Date'[Date]) - 1,

    ALLSELECTED('Date')

)

-------------------------------------------

The max date will return null as there is no max date of 31/12/2023 in the sales table what is a better way to rewrite this measure. keeping in view that the filter column should be from a dimension table not a fact table as is recommended.

[Alex_Sawdo · Accepted solution]

Try this instead:

CALCULATE(

    CALCULATE(

        SUM('Sales'[Profit]),

        INDEX(

            -1,

            FILTER(

                ALLSELECTED(

                    'Date'[Month]

                ),

                NOT ISBLANK(CALCULATE('Sales'[Profit]))

            )

        )

    ),

    REMOVEFILTERS(

        'Date'[Date]

    )

)
This DAX will look back exactly 1 row, in this case the month prior in your date table and calculate your measure for that date range. The only downside to this is you will need to have a "month" column in your date dimension. The REMOVEFILTERS function is in place to ignore any filters in place on your date column, but keeping all other filters. 

[Greg_Deckler]

@akhaliq7 A couple potential problems with this. First, subtracting 1 from a date does not give you last month, it just gives you the previous day. Are you trying to calculate the sales for the last month in the fact table that is within the range of your slicer? If that is the case then maybe something like this:

Measure =
  VAR __MaxDate = MAX('Sales'[Date])
  VAR __YearMonth = YEAR(__MaxDate) * 100 + MONTH(__MaxDate)
  VAR __Table =
    SUMMARIZE(
      FILTER(ALLSELECTED('Sales'), YEAR([Date]) * 100 + MONTH([Date]) = __YearMonth),
      [Date],
      "__Profit", SUM('Sales'[Profit])
    )
  VAR __Result = SUMX( __Table, [__Profit])
RETURN
  __Result