Show L12M totals for each month in the the last x-months, based on user date selection

[bravespiano5]

I've looked everywhere (here, various YouTube channels) and I cannot find a solution. 

The goal is to show what our current L12M totals are for each of the past 13 months, according to the month selected by the user.  So as an example, user selected the month of Oct-21 so show Nov-20 to Oct-21 and the L12M result for each month) 

I have a DateDim table that is connected to my Fact Table and a separate table "DynamicDate" that is connected to the DateDim table but with an inactive Many:1 relationship.

I've combined DAX that I found between SQLBI and GorillaBI, shown below. The dynamic period works perfectly, however the results only populates a monthly result and not the L12M result for each month. 

Any ideas? 🙃

VAR CYYTD = 
    CALCULATE (
        FactTable[Amount],
        CALCULATETABLE (
            DATESINPERIOD ( DateDimension[Date], MAX ( DateDimension[Date] ), -12, MONTH ),
            DateDimension[IsFutureDate] = FALSE ()
        )
    )
VAR MaxDate = MAX(DateDimension[Date])
VAR MinDate = EOMONTH(MaxDate, -13)
Var TimePeriod = 
    DATESINPERIOD(
        DynamicDateDimension[Date],
        MaxDate,
        -13,
        MONTH
    )
Var Result = 
    CALCULATE(
        CYYTD, 
        REMOVEFILTERS (DateDimension [Date]),
        KEEPFILTERS( TimePeriod),
        USERELATIONSHIP(DateDimension[Date], DynamicDateDimension[Date])
    )

Return
If(
    HASONEVALUE(DynamicDateDimension[YearMonthName]) && 
    max(DynamicDateDimension[Date]) <= MaxDate && 
    MIN(DynamicDateDimension[Date])>= MinDate, 
    Result, 
    BLANK()
)

[bravespiano5]

@amitchandak Your suggestion was very helpful!

Below is the DAX I used for the measure. I setup a DynamicDateDimension table that is stand-alone and independent of my model and dims/facts. I then setup a slicer with the year/month based on this DynamicDateDimension table. This allows me to show the L13M based upon the user's date selection and also show the measure as MTD, YTD, L3M, L12M, etc. for each of the L13M shown in the visual.

VAR MaxDate = MAX(DynamicDateDimension[Date]) //Independent and stand-alone date table
VAR MinDate = EOMONTH(MaxDate, -13)
Var Result = 
    CALCULATE(
        FactTable[Amount],
        filter(DateDimension, DateDimension[Date] >= MinDate && DateDimension[Date] <= MaxDate)
    )

Return
If(
    HASONEVALUE(DateDimension[YearMonthName]) && 
    max(DateDimension[Date]) <= MaxDate && 
    MIN(DateDimension[Date])>= MinDate, 
    Result, 
    BLANK()
)

Business requirements also needed me to show results based only on the current month (i.e. CM, PM, YTD, PY YTD, etc.) in the same dashboard. Since all of my measures are based on the DateDimension table connected to my dims/facts, this "CM" visual was showing every month in the DateDim table and I had to filter this down to only the month selected in the DynamicDateDimension slicer.

I was able to create the below DAX and set it to "1" against this "CM" slicer and now my dashboard works perfectly! It took me 1.5 years to finally solve this problem!! ‌‌😁

IF (
MAX ( DynamicDateDimension[date] ) = MAX ( DateDimension[date]),
1,
0
)

[v-janeyg-msft]

Hi, @bravespiano5 

---

however the results only populates a monthly result and not the L12M result for each month. 

---

What does it mean? What's the difference between the result and what you want? I don't understand your needs. What's your visual and context?
There is no syntax error in your formula, if you don't provide sample data, it is difficult for us to judge the problem.

If @amitchandak reply doesn't work, can you share some sample data and your desired result? So we can help you soon.

Janey

[amitchandak · Accepted solution]

@bravespiano5 ,

// 13 month duration should be selected or you need indepedent table in slicer
measure = //13 month is selected
VAR CYYTD =
CALCULATE (
FactTable[Amount],
CALCULATETABLE (
DATESINPERIOD ( DateDimension[Date], MAX ( DateDimension[Date] ), -12, MONTH ),
DateDimension[IsFutureDate] = FALSE ()
)
)
VAR MaxDate = MAXX(DateDimension, DateDimension[Date])
VAR MinDate = MInX(DateDimension, DateDimension[Date])
return
calculate(CYYTD, filter(DateDimension, DateDimension[Date]>=_min && DateDimension[Date] <= _max)
//or
// calculate(sumx(filter(DateDimension, DateDimension[Date]>=_min && DateDimension[Date] <= _max) ,CYYTD))

measure = //One Month selected
VAR CYYTD =
CALCULATE (
FactTable[Amount],
CALCULATETABLE (
DATESINPERIOD ( DateDimension[Date], MAX ( DateDimension[Date] ), -12, MONTH ),
DateDimension[IsFutureDate] = FALSE ()
)
)
VAR MaxDate = MAXX(DateDimension1, DateDimension1[Date])  //independent table
VAR MinDate = eomonth(MaxDate,13)
return
calculate(CYYTD, filter(DateDimension, DateDimension[Date]>=_min && DateDimension[Date] <= _max)
//or
// calculate(sumx(filter(DateDimension, DateDimension[Date]>=_min && DateDimension[Date] <= _max) ,CYYTD))