CODEWITHSUNDEEP
powerbidax
Aastha Jha
Aastha Jha

Reputation: 303

DAX Previous Year Normalized Amount

I have a column called "Normalize Data" which has 2 values "yes" and "no".

enter image description here

Requirement:

When the user clicks on "yes", I want my measure(called Prev_YR_Trans) to do one thing, but if the user clicks "no" I want the measure to do another thing.

Let me explain with an example - My measure Prev_YR_Trans displays the transactions for the previous time period. Let's the say current time period is "25", then it will display the transactions for time period "24". Now, if the user clicks on "Yes", then I want the previous year's transactions to get "normalized" i.e. I want it to get multiplied by the variance b/w the 2 time periods.

What I have tried:

Prev_YR_Trans = 

#getting the current & previous time period from Table "X"
VAR prevSeason = CALCULATE(MAX('X'[Time Period]))-1
VAR maxSeason = CALCULATE(MAX('X'[Time Period]))

#getting variance b/w prev and current time periods
VAR maxSeason_footfall = CALCULATE(SUM('Y'[Park_Footfall]),'Y'[Time Period]=maxSeason)
VAR prevSeason_footfall = CALCULATE(SUM('Y'[Park_Footfall]),'Y'[Time Period]=prevSeason)
VAR footfall_variance = 1+((maxSeason_footfall-prevSeason_footfall)/prevSeason_footfall)

#trying to get the option that the user clicks on(?)
VAR bb = CALCULATE(('X'[Normalize data]))

#returns normalized numbers if user chooses "Yes" else returns actual numbers
RETURN 
IF(bb="Yes",
CALCULATE(SUM('X'[Receipt Count]),'X'[Time Period]= prevSeason)*footfall_variance,
CALCULATE(SUM('X'[Receipt Count]),'X'[Time Period]= prevSeason)
)

In my above measure, the "VAR bb = CALCULATE(('X'[Normalize data]))" is giving me an error as it needs some aggregation like max,min,sum,etc.

How do I resolve my measure so that it displays the correct numbers?

Edit (Solved) More on "Normalize Data" column:

I've solved the "Normalize Data" column by creating a new table called "Normalize Slicer" with yes/no values. Here is the link

Also, if you could help me out with my "Normalize Data" column too then that would great!

So with the "Normalize Data" column - I just want it to display 2 options "Yes" and "No" but I realized I need to create it as a column for the slicer functionality to work.

So for my formula I'd like to display "Yes" if the "Time Period" is less than or equal to the previous time period and "No" otherwise.

I tried to do the following:

Normalize data = IF('X'[Time Period]<=(CALCULATE(MAX('X'[Time Period]))-1),"Yes","No")

But above column just displays "No" for all the values so as a workaround I manually entered the previous time period in the formula:

Normalize data = IF('X'[Time Period]<=24,"Yes","No")

The above formula does work but I'd like it to be dynamic and not a manually entered value.

Upvotes: 0

Views: 85

Answers (1)

msta42a
msta42a

Reputation: 3741

Insted of

VAR bb = CALCULATE(('X'[Normalize data]))

Use this:

var bb = SELECTEDVALUE('X'[Normalize data])

https://dax.guide/selectedvalue/

Upvotes: 1

Related Questions