Week over Week % using a WEEKEND Date column instead of DATE column

[dyttam]

Hello, 

I am looking to calculate Week over Week % using the following measure: 

I think the issue is with the WEEKEND column I am using as the Date reference.

The WEEKEND column is taking calendar days and grouping them into weeks. Which is fine.

The problem, I think, comes at the MIN ('dwh SCFinance_Weekly_WIP'[WEEKEND]) - 1) part of my measure. 

I cannot explain why the result is showing 0.00%, no matter what WEEKEND I select in my slicer. 

I've attached a visual  that might be helpful. 

[dyttam]

I added in an IF statement that would return "N/A" if the VAR "LastWeekValue" = 0. 

It looks like I am correct that the error is in the Filtering part of the DAX as all entries showed up as N/A. 

[v-jingzhang · Accepted solution]

Hi @dyttam , try changing your MIN ('dwh SCFinance_Weekly_WIP'[WEEKEND]) - 1) to MIN ('dwh SCFinance_Weekly_WIP'[WEEKEND]) - 7) since the WEEKEND column is of date type.

Otherwise, you can also use a week number column instead and then it will be something like MIN ('dwh SCFinance_Weekly_WIP'[WEEKNUM]) - 1) to get the previous week's value. Reference: https://community.powerbi.com/t5/Desktop/Change-this-week-over-last-week/m-p/1377285#M587828

[richbenmintz]

Hi @dyttam ,

What is the value of min(table[weekend]) -1, pretty sure it will not be last week value, likely will be the day before. Once you get the correct weekend value i would imagine it will work properly. If you are able to share either the PBIX or some sample data that would be helpful.

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!