Column flag with DAX

[Anonymous]

Hello,

I am trying to create a simple flag column using DAX and I am having trouble getting the result that I want. Here is the scenario:

In my source dataset I have initiativeID's that are related to a specific week, and a flag for records in the current week. I would like to add a flag for records that are in the previous week. I've been trying to work this out in DAX with no success. I feel like what I have should work but for some reason it doesnt. My thought process was to look at each ID, and if it is exactly 1 less than the ID of the current week, then flag those records as 1. Otherwise flag as 0. If I pull out just the part where I am subtracting 1 and put that into a separate card, I get the correct value of 10071. Both the ID and previous week flag are currently set to whole number types. Below is a screenshot that highlights exactly which records I want to be flagged as 1.

isPreviousWeek =
IF (
    InitiativeOverview_Dimension[initiativeid]
        = CALCULATE (
            DISTINCT ( InitiativeOverview_Dimension[initiativeid] ),
            InitiativeOverview_Dimension[isCurrentWeek] = 1
        ) - 1,
    1,
    0
)

Any ideas?

Thanks!

[Anonymous]

Some variables and the use of MAX might be the solution you're looking for.

Something to this effect:

isPreviousWeek = 
VAR previous_id = CALCULATE(
MAX(InitiativeOverview_Dimension[initiativeid]),
ALL(InitiativeOverview_Dimension[initiativeid])) - 1
RETURN
IF ( 
SELECTEDVALUE(InitiativeOverview_Dimension[initiativeid]) = previous_id,
1, 0 )

[Anonymous]

Thanks! I will check out that file.

@Anonymous Thank you for the suggesstion and trying to help me work through it. Unfortunately the upcoming week is loaded towards the end of each week. Therefore the MAX ID will change and the current week temporarily does not have the MAX ID.

[amitchandak]

@Anonymous ,

In case the file did not help you let me know

[Anonymous]

Hey @amitchandak 

Unfortunately my organization blocks dropbox so I'm not able to download the file.

Even if that works, I would really like to know why my DAX statement above does not work. I'm trying to get a deeper understanding of the language. To me, what I have makes sense.

[Anonymous]

Just wanted to try one more time to see if anyone knows why this statement does not provide the expected result described in my initial post:

isPreviousWeek =
IF (
    InitiativeOverview_Dimension[initiativeid]
        = CALCULATE (
            DISTINCT ( InitiativeOverview_Dimension[initiativeid] ),
            InitiativeOverview_Dimension[isCurrentWeek] = 1
        ) - 1,
    1,
    0
)

Thanks!

[amitchandak]

Refer this file. I created a calendar table and created week wise rank. Your last week max rank -1. Calculated this week vs last week too

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0