DAX formula help, WoW percent change

[GrahamCracker19]

Looking for some help with a DAX formula to calculate the WoW percent change. I have the formula below but the card visual doesn't have any value displayed and I cannot figure out my error. Any help is much appreciated.

% Change Sales = 
VAR CurrentValue = SUM('Sales'[Units])
VAR PreviousValue =
SWITCH(
    TRUE(),
    ISINSCOPE('Sales'[Report Date]), CALCULATE((SUM('Sales'[Units])), DATEADD('Sales'[Report Date].[Day], -7, DAY))
)
RETURN
DIVIDE(
    CurrentValue - PreviousValue,
    PreviousValue
)

 

[Matt Allington]

What is the purpose of ISINSCOPE?

When writing time intelligence measures, never ever use a card to test it. Set up a table (or matrix) with time progression on rows. In your case you need either weeks or days on rows. Then add your measure to the table. Doing it this way will show you what your measure is doing. My guess is your measure works but it doesn’t work for the last date in the filter context.

 

[GrahamCracker19]

I'm very much a novice with DAX and was researching how to write the code for what I wanted. One of the walk through videos that I found had that in there, I used the code he provided in his video to calculate the value I was looking for.

I will try as a table and let you know the result.

 

[GrahamCracker19]

@Matt Allington I changed the visual to a table and still get no data in the table. Is there a better way to calculate the measure?

 

[Matt Allington]

Did you remove "INSCOPE"? I have no idea what you are trying to do with that function.
Also, you would be better using your own calendar table.

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

DAX Time Intelligence Explained

Level: Beginners I help a lot of people on forums who ask questions about time intelligence for DAX. If you are just starting out then the chances are that you may not even be clear what time intelligence is and hence sometimes you don’t even know what to ask. Often [...]Read More »

exceleratorbi.com.au

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au