DAX Calculate filtering

[JPElmer]

I hope this is the proper forum for this request.

I am struggling with an incorrect result from a DAX Calculated measure using Excel 2013 and Power Pivot. The measure that works, but results in an incorrect answer is as follows:

PriorCount:=
CALCULATE (
COUNTROWS ( EquipmentList ),
EquipmentList[InServiceDate] <> BLANK (),
FILTER (
VALUES ( EquipmentList[InServiceDate] ),
EquipmentList[InServiceDate] <= [PriorEndDate]
),
FILTER (
VALUES ( EquipmentList[SoldDate] ),
OR (
EquipmentList[SoldDate] > [PriorEndDate],
EquipmentList[SoldDate] = BLANK ()
)
)
)

(Thank you DAX Formatter)

The measure [PriorEndDate] appears to be the culprit. In the context from the Power Pivot table where this is used, the user selects a date range on a Timeline Slicer. Depending on the selection, [PriorEndDate] is either :

PriorPeriodMonth:=
LASTDATE (
DATEADD (
DateDimension[Date],
[SlicerMonthCount] * [SlicerInterval],
MONTH
)
)

or

PriorPeriodYear:=
LASTDATE ( DATEADD ( DateDimension[Date], -1, YEAR ) )

as determined by :

PriorEndDate:=SWITCH (
[SwitchValue],
0, [PriorPeriodMonth],
1, [PriorPeriodYear]
)

The best I can figure out is that the Calculate function in [PriorCount] is losing the Pivot Table context in some manner. With the current data I am using, the value for [PriorEndDate] when the current year to date is selected should be 7/31/2014 and return a count of 137. However, [PriorCount] is returning a count of 129 which is the value for 12/31/2014.

I am sure this is not the clearest explanation. Bottom line is the user selects current YTD and should see data for the previous YTD in the same period, not for the full previous year.

Any thoughts would be appreciated.

Thank you

 

[JPElmer]

Incidentally, if I put the cubevalue of a measure in a worksheet for the various date measures, they return correct values. Additionally, if I replace [PriorEndDate] with [PriorPeriodYear] in the [PriorCount] measure (bypassing the Switch measure) and select the current YTD, it returns the correct value for the previous YTD.

This is very puzzling to me. I have poured over Marco Russo's excellent article on "How Calculate works in DAX" and felt I was doing things right to retain the pivot table filter context but I must be missing a detail and the Calculate / Filter combination is stripping the filter context coming into the measure.

Thanks again.

 

[JPElmer]

Going to take 1 more stab at this ...

I have reworked some background data to eliminate any blanks in the 2 date fields and therefore do not need to trap for Blank(). I have combined all the selection measures into 1 IF() statement. The following measure should shift the Date context back either 1 year or X - months.

PriorEndDate:=
IF (
    AND (
        MONTH ( FIRSTDATE ( DateDimension[Date] ) ) = 1,
        MONTH ( LASTDATE ( DateDimension[Date] ) )
            = MONTH (
                CALCULATE (
                    LASTDATE ( RMData[TransactionDate] ),
                    ALL ( DateDimension[Date] )
                )
            )
    ),
    LASTDATE ( DATEADD ( DateDimension[Date], -1, YEAR ) ),
    LASTDATE (
        DATEADD (
            DateDimension[Date],
            DISTINCTCOUNT ( DateDimension[Month] ) * -1,
            MONTH
        )
    )
)

This value is then used to evaluate the 2 date fields in my "EquipmentList" table and produce a count of units with the following measure.

PriorCount:=
CALCULATE (
    COUNTROWS ( EquipmentList ),
    FILTER (
        VALUES ( EquipmentList[InServiceDate] ),
        EquipmentList[InServiceDate] <= [PriorEndDate]
    ),
    FILTER (
        VALUES ( EquipmentList[SoldDate] ),
        EquipmentList[SoldDate] > [PriorEndDate]
    )
)

The Timeline slicer being used on the pivot table is keyed to my DateDimension table on the Date key of contiguous dates. It responds as a disconnected slicer in this instance as there is no relationship between the DateDimension table and the EquipmentList table.

As I stated before, the problem is I am not getting correct values for a YTD shift. The measures work and in different context produce correct values but for the life of me I cannot figure this out.

Any help out there?

Thanks

 

[scottsen]

Ya know, I called Marco into the forum a week or two ago for *my* problem which... wasn't super similiar, but had me confused in a similiar way in the "once I am inside THIS measure, what context am I using!?" sorta thing. I will try calling him in again for us

That said, I would probably think about adding a "helper" calculated column to simplify your life. Like... can PriorEndDate be precalculated and stored in a column is it dynamic based on some slicers?

 

[JPElmer]

Ya know, I called Marco into the forum a week or two ago for *my* problem which... wasn't super similiar, but had me confused in a similiar way in the "once I am inside THIS measure, what context am I using!?" sorta thing. I will try calling him in again for us

That said, I would probably think about adding a "helper" calculated column to simplify your life. Like... can PriorEndDate be precalculated and stored in a column is it dynamic based on some slicers?

scottsen

Thanks for the reply. I am trying to make this dynamic. The whole exercise is an attempt to de-clutter a potential dashboard / report worksheet. Currently I have the usual Year / Quarter / Month slicers that the user can filter with. I am attempting to get the same functionality out of 1 "Timeline slicer" and everything works fine except the 1 year back portion.

Been working on it some more this morning and I suspect the "ALL (DateDimension[Date])" statement that is nested in my IF() function. If I replace that ALL with the month number for the last transaction date (currently 7) then it all seems to work. But if I don't clear the filter context to get that value the last transaction month moves with the slicer.

I just don't see why that ALL should effect any other CALCULATE function but I suspect it does. The other puzzling aspect is I can pull a cubevalue for the LastTransDate and PriorEndDate in the worksheet and they both display correctly.

If you have an "in" with Marco, I would love to hear any thoughts he may have.

Thanks
Pat

 

[marco.russo]

Can you post the YTD measure you are using?
Is there a relationship (direct or indirect) between RMData and EquipmentList?

 

[JPElmer]

I am actually trying to be sneaky on the YTD. Using the timeline slicer if the user chooses January (current year) to end of period (currently July) I am grabbing the selection with ...

IF (
    AND (
        MONTH ( FIRSTDATE ( DateDimension[Date] ) ) = 1,                    ; Is start January?
        MONTH ( LASTDATE ( DateDimension[Date] ) )                            ; Is end equal to last month in fact table?
            = CALCULATE (
                MONTH ( LASTDATE ( RMData[TransactionDate] ) ),              ; RMData is fact table
                ALL ( DateDimension[Date] )
            )
    )

and moving either back one year for YTD ...

    LASTDATE ( DATEADD ( DateDimension[Date], -1, YEAR ) ),

or back by the number of months selected ...

    LASTDATE (
        DATEADD (
            DateDimension[Date],
            [SlicerMonthCount] * -1,
            MONTH
        )
    )

Using ...

SlicerMonthCount:=DISTINCTCOUNT ( DateDimension[Month] )

to grab the number of months selected.



There is an implicit (?) relationship between fact table RMData and dimension table EquipmentList on column UnitID.

As a side note I am also using other measures to present the expense, CPM etc. for the given period using similar to ...

PriorCostYear:=
CALCULATE (
    [TotalCost],
    DATEADD (
        DateDimension[Date],
        -1,
        YEAR
    )
)

This works fine with the same kind of range selection I am trying to do for the PriorCount function.

Hope you see something . I really appreciate you taking a look.

Thank you
Pat

 

[marco.russo]

Sorry, but I still don't see where is the YTD calculation - maybe in TotalCost measure?
DATEADD is just a shift in dates, LASTDATE returns a single day, for the YTD you have to extend the range of dates from the initial selection, right?
You said that YTD was not working and other measures were ok.
Thanks,

Marco

 

[JPElmer]

Excuse me ... I meant an explicit relationship between RMData and EquipmentList on UnitID. There is an implicit relationship between EquipmentList and DateDimension through RMData.

Thanks

 

[marco.russo]

ok but you didn't answer to the YTD question.
can you post a diagram of the relationships? It's not clear the direction.