DAX time intelligence functions with nested filters

Question

I have a data structure like this

DateRoll    Dataset Date    Value   Customer
Month   Online  1/1/2018    10  Cust1
Month   Online  2/1/2018    11  Cust1
Month   Online  3/1/2018    12  Cust1
Month   Online  4/1/2018    22  Cust1
Quarter Online  1/1/2018    33  Cust1
Quarter Online  4/1/2018    22  Cust1

I have to calculate previous quarter value, I tried different ways but it's not working

1 - Not returning any value.

CALCULATE (
        SUM ( 'Data_Rollup_KPI_DNR'[Value] ),
        DATEADD ( 'Data_Rollup_KPI_DNR'[Date].[Date], -1, QUARTER ),
        FILTER ( Data_Rollup_KPI_DNR, Data_Rollup_KPI_DNR[DateRoll] = "Quarter")
    )

2--Nested - Returning overall total

CALCULATE (
        CALCULATE (
            SUM ( 'Data_Rollup_KPI_DNR'[Value] ),
            DATEADD ( 'Data_Rollup_KPI_DNR'[Date].[Date], -1, QUARTER )
        ),
        FILTER ( Data_Rollup_KPI_DNR, Data_Rollup_KPI_DNR[DateRoll] = "Quarter" )
    )

3--Nested --Returning overall total

CALCULATE (
        CALCULATE (
            SUM ( 'Data_Rollup_KPI_DNR'[Value] ),
            FILTER ( Data_Rollup_KPI_DNR, Data_Rollup_KPI_DNR[DateRoll] = "Quarter" )
        ),
        DATEADD ( 'Data_Rollup_KPI_DNR'[Date].[Date], -1, MONTH )
    )

Tried PREVIOUSQUARTER function too, but its not returning any value.

Answer 1

To take advantage of built in DAX time intelligence functions you will need to to have a contiguous set of dates. I would recommend using a date table. The following code can be used to create a date/calendar table in your model:

Celndar = 
Var MinDate = MIN(Data_Rollup_KPI_DNR[Date])
Var MaxDate = MAX(Data_Rollup_KPI_DNR[Date])
Var BaseCalendar = CALENDAR(MinDate, MaxDate)
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR YrMonth =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
        VAR Qtr =
            CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
        RETURN
            ROW (
                    "Day", BaseDate,
                    "Year", YearDate,
                    "Month Number", MonthNumber,
                    "Month", FORMAT ( BaseDate, "mmmm" ),
                    "Year Month", FORMAT ( BaseDate, "mmm yy" ),
                    "YrMonth", YrMonth,
                    "Qtr", Qtr
            )
    )

Once this table exists, mark it as a 'date' table and create a relationship with

Data_Rollup_KPI_DNR[Date]

Then, you can write the following measure to obtain the results you are searching for:

PQSum =
CALCULATE (
    SUM ( 'Data_Rollup_KPI_DNR'[Value] ),
    PREVIOUSQUARTER ( 'Calendar'[Date] )
)

Hope that helps!

*Edited

You can also create a ranking column to index in a measure:

Rank =
RANKX (
    FILTER (
        'Data_Rollup_KPI_DNR',
        'Data_Rollup_KPI_DNR'[DateRoll] = EARLIER ( 'Data_Rollup_KPI_DNR'[DateRoll] )
    ),
    'Data_Rollup_KPI_DNR'[Date].[Date],
    ,
    ASC
)

Then you can reference a previous quarter using something like the following:

PQSum2 =
CALCULATE (
    SUM ( 'Data_Rollup_KPI_DNR'[Value] ),
    FILTER (
        'Data_Rollup_KPI_DNR',
        'Data_Rollup_KPI_DNR'[Rank]
            = MAX ( 'Data_Rollup_KPI_DNR'[Rank] ) - 1
    ),
    'Data_Rollup_KPI_DNR'[DateRoll] = "Quarter"
)

but this is hard coded and just plain nasty!

Answer 2

Echoing @steliok that a date dimension is the proper way to handle this; there are plenty of date table templates out there, and a date dimension will work with your data model. If you really really can't add to your data structure for some reason, this should work:

BaseValue = SUM ( 'Data_Rollup_KPI_DNR'[Value] )

PriorQuarter =
VAR CurrentDate = MAX ( 'Data_Rollup_KPI_DNR'[Date] )
VAR CurrentYear = YEAR ( CurrentDate )
VAR CurrentMonth = MONTH ( CurrentDate )
VAR FirstMonthOfCurrentQuarter =
    SWITCH (
        TRUE (),
        CurrentMonth IN {1,2,3}, 1,
        CurrentMonth IN {4,5,6}, 4,
        CurrentMonth IN {7,8,9}, 7,
        CurrentMonth IN {10,11,12}, 10
    )
// DATE() does the right thing with negative month args
VAR PriorQuarterDate = DATE ( CurrentYear, FirstMonthOfCurrentQuarter - 3, 1 )
RETURN
    CALCULATE (
        [BaseValue],
        ALL ( 'Data_Rollup_KPI_DNR'[DateRoll], 'Data_Rollup_KPI_DNR'[Date] ),
        'Data_Rollup_KPI_DNR'[Date] = PriorQuarterDate,
        'Data_Rollup_KPI_DNR'[DateRoll] = "Quarter"
    )

This relies on DATE being clever, which it is. DATE ( 2019, -2, 1 ) = DATE ( 2018, 10, 1 ).

Ultimately, my question is why can't you just source the un-rolled up data from the same place that the ETL process is sourcing it?

Answer 3

Date functions are working well when you are using @ Day level.

Following link would be helpful to resolve your issue,

https://community.powerbi.com/t5/Desktop/Lead-and-Lag-in-DAX/td-p/649162