Forum Discussion
UseRelationship at the day level is not working (sample model attached)
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.
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!!
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.
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.