Calculation Group: Different filters for Current period and previous periods in aggregate time intel

[JohnShepherdAPD]

Hi fellow dax enthusiasts

I have KPI measures that I want to calculate the YTD and Rolling twelve (R12) values, however, the latest period needs to have extra filters compared to the previous x periods 

VAR _Agg =
    SELECTEDVALUE ( 'Slice_Aggregate'[Index] )
VAR _VerType =
    SELECTEDVALUE ( 'Slice_VersionTypes'[Index] ) 
//LP = Latest Period
VAR _MaxDateLP =
    EOMONTH ( MAX ( 'Dimension Date'[FullDateAlternateKey] ), 0 )
VAR _MinDateLP =
    EOMONTH ( _MaxDateLP, -1 ) + 1
VAR _ResultLP =
    IF (
        _VerType = 1,
        CALCULATE (
            SELECTEDMEASURE (),
            FILTER (
                ALL ( 'Dimension Date' ),
                'Dimension Date'[FullDateAlternateKey] <= _MaxDateLP
                    && 'Dimension Date'[FullDateAlternateKey] > _MinDateLP
            ),
            'Fact DealerCompositeData'[IsLatestVersionSubmitted] = TRUE,
            'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE,
            'Fact DealerCompositeData'[IsSuppressed] = FALSE
        ),
        CALCULATE (
            SELECTEDMEASURE (),
            FILTER (
                ALL ( 'Dimension Date' ),
                'Dimension Date'[FullDateAlternateKey] <= _MaxDateLP
                    && 'Dimension Date'[FullDateAlternateKey] > _MinDateLP
            ),
            'Fact DealerCompositeData'[IsLatestVersionOntime] = TRUE,
            'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE,
            'Fact DealerCompositeData'[IsSuppressed] = FALSE
        )
    ) 
//PP = Preceding periods
VAR _MaxDatePP =
    EOMONTH ( MAX ( 'Dimension Date'[FullDateAlternateKey] ), -1 )
VAR _MinDatePP =
    EOMONTH ( _MaxDatePP, -11 ) + 1
VAR _ResultPP =
    CALCULATE (
        SELECTEDMEASURE (),
        FILTER (
            ALL ( 'Dimension Date' ),
            'Dimension Date'[FullDateAlternateKey] <= _MaxDatePP
                && 'Dimension Date'[FullDateAlternateKey] > _MinDatePP
        ),
        'Fact DealerCompositeData'[IsLatestVersionSubmitted] = TRUE,
        'Fact DealerCompositeData'[IsLatestVersionApproved] = TRUE
    )
VAR _MonthCount =
    CALCULATE (
        COUNTROWS (
            SUMMARIZE (
                'Fact DealerCompositeData',
                'Fact DealerCompositeData'[ActivePBIParticipationDateKey]
            )
        )
    )
RETURN
    IF (
        ISNUMBER(_ResultLP),
        IF (
            _Agg = 1,
            _ResultPP + _ResultLP,
            DIVIDE (
                ( _ResultPP * _MonthCount ) + _ResultLP,
                IF ( _Agg = 2, 1, _MonthCount )
            )
        ),
        _ResultLP
    )

to achieve this I created two variables one for the latest period and one for previous periods, this mainly works except I get odd results when my SelectedMeasure is based on an Average and also if I don't check the return value it attempts to convert text (measure names etc) into values hence the ISNUMBER check.

Is there a better way of achieving what I have done but with one variable? I tried to join the FILTER function with && but its not allowed.

note:

when _VerType = 1 = Reported versions

when _VerType = 2 = Latest versions

when _Agg = 1 = SUM

when _Agg = 2 = Average

[amitchandak]

@JohnShepherdAPD , not very clear, you can measure like

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , MAXX(allselecetd('Date'), 'Date'[Date]))
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

refer

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[JohnShepherdAPD]

Hi @amitchandak, thanks for your reply I am comfortable with the various time intelligence I want to use, my problem is having a conditional filter on that time intelligence

I want to do something like this:

for the first 11 months:

[IsLatestVersionSubmitted]=TRUE AND IsLatestVersionApproved=TRUE

for the latest month (final month):

[IsLatestVersionOnTime]=TRUE AND [IsSuppressed]=FALSE AND [IsLatestVersionApproved]=TRUE

of course I get this error:

"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."