UseRelationship at the day level is not working (sample model attached)

[jordancole517]

I've attached a very simple model for you all to be able to see what is going on quicker! 🙂

PowerBI Sample File 

I'm sure I'm just a bonehead and missing something simple, but I'm attempting to use the UseRelationship function to form a relationship between two identical date tables.

I'll then use one filter from each date table in order to be used in my. The calculations work perfectly at the week level, but when I expand it down to the day the value for the entire week shows for each day.

The field that is incorrect changes depending on which date table I grab my week field from to use in my matrix. There are only two measures.

Simple sum measure with no userelationship

Date Sum = SUM('Data Table'[Amount])

Attempt to use the userelationship

Previous Date Sum =

 CALCULATE(

     [Date Sum]

     ,ALL('Date')

        ,USERELATIONSHIP('Date'[Date],'Previous Date'[Date])

 )

[jordancole517]

Thank you, Ashish. I have two separate slicers so users can determine the two different time periods they're comparing. 

This is part of a sales mix analysis I have so there are variances which are calculated between the two dates.

In attempting to use your suggestion in my sample file I find that if I was to take away the "ALL" in the calculate function as you suggest and choose a week that is not the same in both slicers then the value for the previous date sum turns blank.

I've attached an updated file that shows this result. The "Date Sum" measure should be summing based on the "Date" filter and the "Previous Date Sum" measure should be based on the "Previous Date" filter.

PowerBI - no ALL and different weeks selected

Thank you!!

[jordancole517 · Accepted solution]

I think the answer is just that I need to have an ALLEXCEPT instead of the ALL in my calculate statement. I guess it matters because the values are the same in both filters. Said another way they are both spelled the same way and appear the same vs the week dates which have the week and year and, therefore, are unique to each filter. 

Previous Date Sum =

 CALCULATE(

     [Date Sum]

        ,ALLEXCEPT('Date','Date'[Day of Week Name])

        ,USERELATIONSHIP('Data Table'[Business Date],'Previous Date'[Date])

 )

Attaching a sample file for anybody that comes across the same issue. Seems like a very common use case for time comparisons that aren't always something simple like YoY, WoW, etc. I also have to use a retail calendar, so I can't use these naturally embedded functions. This will work for retail calendared folks as well.

NOTE: The variance calculations in this file don't have any kind of weighting/normalization between the two time periods to level set the time periods. If you want to compare time periods of different durations then you should just multiply your calculated sum accounts by a separate normalization factor that you'll create.

Solution! 

[Ashish_Mathur]

Hi,

Remove the ALL() from your formula.  Also, why do you need the Previous Date Table in the first place.  If you wish to get the same on the same day last week, then write this measure

Measure = calculate([sales],datesbetween('Date'[Date],min('Date'[Date])-7,min('Date'[Date])-7))

Hope this helps.

[jordancole517]

Here is the data model