Forum Discussion
Measure as Calculated Column
Hello All, I have been struggling to resolve this.
I have measure which calculates the month difference from 2 dates which are measures. i need to apply that measure value in creating a flag with the below
NewWin=IF ( Sales[cust] = "Not Defined", 0, IF ( Sales[MonthCount] ) < 13, 1, 0 ) )
- Sales[Cust] is a calculated column
- Sales[MonthCount] is a measure
I am able to create this as measure but not in calculated column, I need calculated column so that I can use this as slicer, can anyone please help?
Ashish_Mathur can you please look at this?
3 Replies
Hi,
Measures should not be referred to in calculated column formulas because calculated column formulas do not respond to change in filters/slicers.
Please share a dataset, describe the business question and show the expected result. There could be a way to write a calculated column formula without calling a measure inside it.
Thank you for your response. I am trying to find new customer wins compared in different time periods.
The below table has the logic in step by step for each time frame. TF is time frame.
Logic is, user selects year and month, if the CPDF number exists in the current month, get the earliest revenue date. Then from the current selected period, look back upto 12 months and find the earliest revenue date date, if the earliest invoice date is same as current period, then look back upto one more 12 months and find the earliest revenue date, if exists then check if the difference between this date and originally selected period is less than 13 months, if yes, then its a new win.
I am able to create this logic and measures and owrks perfectly, however the challenge is I need to be able to select the New win as filter or slicer, so that user can select year, month and new win as yes or no. But I am not able to use measure as column to apply in filter/slicer.
Really appreciate your help in advance Ashish_Mathur
Here is the test file. https://1drv.ms/u/s!AoC3T84cA7LGjl3HbemEMeGx29Y_
Customer Parent number & Draws Combined Number (CPDF) Start Date End Date FIRST EARLIEST Revenue DATE IN Selected Period TF1 Start Date TF1 End Date "TF1 Date" TF2 Start Date TF2 End Date TF2 Date End Date of TF2 Date Month Difference (TF2 and Selected Period) New Win ? 63759DF16 12/1/2018 12/31/2018 12/2/2018 12/31/2018 1/1/2018 12/4/2017 12/31/2017 1/1/2016 1/1/2016 1/31/2016 36 No Hi,
I will not be able to help here because one cannot refer to a measure within a calculated column formula.
