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]

Sorry ... I'm rather new to DAX and may be trying to do something I shouldn't.

That being said, I'll try to explain. I am using a Power Pivot based around my fact table RMData to sum and total expense per unit (semi-trucks) over a period of time. The data extends from Dec. 2010 to current. I am using the DateDimension table to relate the fact table to various periods as selected in a Timeline slicer tied to DateDimension[Date] the key column of the dimension table. This slicer is connected to the Pivot table. I can perform SUM calculations on the data for the current period selected and further calculate Cost per Mile and Cost per Unit per Month using simple math functions. My problem is assigning an accurate unit count for a given period as trucks are sold or added over time. The measure PriorCount (listed below in a previous post) compares the InServiceDate and SoldDate from the EquipmentList table to the Timeline slicer selection and determines if the unit was active for the period and includes it in the count.

I am using the same slicer for both the current period value as well as the prior period value. Everything works right except the unit count when the user selects the current YTD on the slicer i.e. January through July. There is no actual YTD measure or function, I am just trying to trap the values off the slicer and using the Pivot Table to present the data within that context. Best I can determine when I select Jan - Jul 2015 I am getting the unit count for Dec. 2014 not July 2014. My suspect is the comparison of LastDate(DateDimension[Date] to LastDate(RMData[TransactionDate] with the ALL filter applied. If I replace ...

= CALCULATE (
                MONTH ( LASTDATE ( RMData[TransactionDate] ) ),
                ALL ( DateDimension[Date] )

with a hard coded value of 7, it works just fine. If that ALL filter continues to effect the result if True and result if False of the IF() function it's all nested in then I can see the date moving to Dec 2014. This is just not an expected behavior if in fact it's happening.

Anyway, a very long winded garbled explanation that I hope makes sense.

Thank you again

 

[marco.russo]

Ok so you are not using YTD
Can you provide the schema of your model? A picture of the relationships you have would be useful.

 

[JPElmer]

I Hope this works ... never uploaded a pic here before.

I have indicated the primary relationship connections with the straight lines. I'm ignoring all the attendant dimension tables that are not relevant at this time. Hope this makes sense.

Thank you
Pat

 

[marco.russo]

Ok, if you want to get the last date in RMData, using this syntax:

                CALCULATE (
                    LASTDATE ( RMData[TransactionDate] ),
                    ALL ( DateDimension[Date] )
                )

you don't remove possible filters existing from other columns.

                CALCULATE (
                    LASTDATE ( RMData[TransactionDate] ),
                    ALL ( RMData )
                )

I don't have time to rewrite the entire syntax (also because I am not convinced about your approach, but I don't question why you want to obtain such a behavior) - but if you want to simply get the last date available in the entire RMData table, you have to remove any other filter. If you want to selectively get the last date (local to a certain selection), you have to identify which other filters you want to remove (you have filters from EquipmentList, PartClass, ComponentCode, JobCode, and other exposed columns of RMData, too).

 

[JPElmer]

Well, you nailed it. I see the error I was making now. Sometimes it's just a forest and trees thing.

I do appreciate your assistance.

Thank you
Pat