measure based on a variable from slicer

[Anonymous]

Hi, I am having a problem with the calculation of measure based on variable from the slicer.

This is the data model, I have Source.Filters which determine the date of the source documents, basing on this table I want to browse through the data sets and identify period/period (source/source) deviations.

I have created a measure to identify the m/m deviation on gross margin but it does not work. In this particular code when I put filter FilterPreviousSourceDate it does not show any data, it works however when I use filter FilterSelectedSourceDate. I cannot work out what might be the problem.

[AlexisOlson]

The table FilterPreviousSourceDate is empty because Projects is already filtered to be the selected date.

Try this for your final CALCULATE instead:

CALCULATE ( SUM ( Projects[Sales] ), Projects[Source.Date] = PreviousSourceDate ) )

Written like this, we replace the filter on that column rather than adding another filter.

[Anonymous]

Just for the record, I also checked that the variables produce correct values by having them side-examined as separate measures only for testing purposes and with the slicer they produce the expected values

[AlexisOlson]

There must be some sort of filtering conflicting with Source Date then. For example, if your My Dates table is filtered then you might again have mutually exclusive filters resulting in a blank result.

I can't really determine specifics without seeing the file.

[Anonymous]

I worked on the file a little bit and it turns out as you were saying that there must be a filtering conflict. When a particular Source.Date is selected on the slicer then apparently no other data set from Projects table for another Source.Date can be calculated and shown as a measure because thecliser allows only the Source.Date selected on the slicer and this one is applied to filter all the data set available (apparently the table must be already filtered). I must think of a way how to dynamically take the data from the same source table with a filter based on the slicer and at the same time be able to show dynamically period-to-period difference in values. The idea behind it was to show period-to-period deviations basing on dynamically changing Source.Date (being set in the slicer).

[Anonymous]

Hi, thanks for suggestion. I have already tried application of filter both ways, the way it was posted i.e.

CALCULATE ( SUM ( Projects[Sales] ), FilterPreviousSourceDate )

and the way you suggested

CALCULATE ( SUM ( Projects[Sales] ), Projects,Projects[Source.Date]=PreviousSourceDate )

and neither worked. What is interesting is that both ways of filtering in the final calculation worked in case of FilterSelectedSourceDate so there must be some problem with the data set to be calculated basing on 

PreviousSourceDate. 

I checked data formatting in tables so that it is the same for matching purposes, I tried LOOKUPVALUE for Source.Period instead of Source.Date (that would do the analytical work too) but the outcome was the same: I cannot produce the data set from previous source/period.

My to-date knowledge of PBI is insuffient to overcome the problem without rebuilding the data model to arrive at the same calculation but through different formula but I would prefer to identify the reason why this one is not working.

In general I need to be able to compare two or more data sets spread overtime to identify source-to-source deviations. I thought that building a Source.FIlter table with dates and index would be best to compare data in two time dimensions (source date and period date, the latter being filtered by the data model calendar table).

[Anonymous]

EDIT:

was:

and the way you suggested

CALCULATE ( SUM ( Projects[Sales] ), Projects,Projects[Source.Date]=PreviousSourceDate )

and neither worked.

Should be:

and the way you suggested

CALCULATE ( SUM ( Projects[Sales] ), Projects[Source.Date]=PreviousSourceDate )

and neither worked.