creating a measure that calculates on a different calculation based on a set date

[maurcoll]

Hi

I am trying to create a measure which dependant on date will change the calculation it uses. I have tried using selectedvalue but this doesnt quite work as i am using in another calculation for the last 12 months

example

Total =

(if (selectedvalue( 'calendar' [date] >= date (2024,6,1) 
measure 1,

measure 2))

This works if i put into a table visual with a date column but i want to be able to use this total in another measure that calculates the total for the last 12 complete months or into a card visual. The data in our source system changed on the 1st June.

This is the measure i use for the last 12 months

Last 12 months = 

VAR maxDate = TODAY() - DAY(TODAY())
VAR minDate = EDATE(TODAY() - DAY(TODAY()), -12)

Return

Total

'Calendar'[Date] <= maxDate && 'Calendar'[Date] > minDate)

[Bibiano_Geraldo · Accepted solution]

Hi @maurcoll ,

Here's the updated Measure for Total:

Dynamic Total = 
VAR CutoffDate = DATE(2024, 6, 1)

RETURN
    IF(
        MAX('Calendar'[Date]) >= CutoffDate,
        [Measure 1], -- Use Measure 1 after June 1, 2024
        [Measure 2]  -- Use Measure 2 before June 1, 2024
    )

Now you can calculate the total for the last 12 months dynamically:

Last 12 Months = 
VAR MaxDate = TODAY() - DAY(TODAY()) -- Last day of the previous month
VAR MinDate = EDATE(MaxDate, -12)    -- 12 months before the last day of the previous month

RETURN
    CALCULATE(
        [Dynamic Total],            -- Use the dynamic total measure
        'Calendar'[Date] <= MaxDate &&
        'Calendar'[Date] > MinDate -- Filter dates in the last 12 months
    )

When you use the Dynamic Total measure in a table or matrix, it dynamically switches between Measure 1 and Measure 2 based on the date condition.
When you use the Last 12 Months measure, it applies the date range filter while also dynamically switching between the measures depending on the context of the dates in the filtered period.

[bhanu_gautam]

@maurcoll To calculate the total for the last 12 complete months, you can create another measure that uses this Total measure.

DAX
TotalLast12Months =
CALCULATE (
[Total],
DATESINPERIOD (
'calendar'[date],
MAX('calendar'[date]),
-12,
MONTH
)
)