User Profile
Alicetemitope
Frequent Visitor
Joined 3 years ago
User Widgets
Contributions
IF statement with a column
Hi Everyone, I am currently working on a report and I want to display results of a measure for a select few of the users based on their user principal name or email address. For example using table below: Measure = VAR Result = sumofacolumn Return If(view Result = "Yes", sumofacolumn, "Result not available to user") The above is what I am hoping to replicate using IF statement in DAX but it seems the IF statement only works with a calculated column. I want to use it in a measure. Please how can I go about this? Thank you. USER Principal Name View Result [email protected] Yes [email protected] No ldkjubfeoiü@gmail.com Yes [email protected] NoComplex DAX suggestion
Hey Guys, I hope you are all doing great. So I started at a new job and inherited this report from a past PBI analyst. I am really confused about this formula and I was wondering if anyone can help explain it or provide a better way of writing it if you are up for the challenge. I am new to PBI myself and I am lost. Thanks Accumulated Projection (6-Month Average) = VAR _Last6MonthsAverageConsumption = [Average Consumption of Last 6 Months] VAR _Last6MonthsAverageConsumptionGrowth = [Avg Amortized Consumption Growth Rate of Last 6 Months] --Values CY VAR _JanuaryCurrentYearValue = [Average Consumption of Last 6 Months] * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _FebruaryCurrentYearValue = _JanuaryCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _MarchCurrentYearValue = _FebruaryCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _AprilCurrentYearValue = _MarchCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _MayCurrentYearValue = _AprilCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _JuneCurrentYearValue = _MayCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _JulyCurrentYearValue = _JuneCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _AugustCurrentYearValue = _JulyCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _SeptemberCurrentYearValue = _AugustCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _OctoberCurrentYearValue = _SeptemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _NovemberCurrentYearValue = _OctoberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _DecemberCurrentYearValue = _NovemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) --Values NY VAR _JanuaryNextYearValue = _DecemberCurrentYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _FebruaryNextYearValue = _JanuaryNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _MarchNextYearValue = _FebruaryNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _AprilNextYearValue = _MarchNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _MayNextYearValue = _AprilNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _JuneNextYearValue = _MayNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _JulyNextYearValue = _JuneNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _AugustNextYearValue = _JulyNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _SeptemberNextYearValue = _AugustNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _OctoberNextYearValue = _SeptemberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _NovemberNextYearValue = _OctoberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) VAR _DecemberNextYearValue = _NovemberNextYearValue * ( 1 + [Avg Amortized Consumption Growth Rate of Last 6 Months] ) RETURN IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 1, _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 2, _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 3, _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 4, _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 5, _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 6, _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 7, _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 8, _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 9, _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 10, _OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 11, _NovemberCurrentYearValue + _OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) && SELECTEDVALUE ( DIM_Date[Month] ) = 12, _DecemberCurrentYearValue + _NovemberCurrentYearValue + _OctoberCurrentYearValue + _SeptemberCurrentYearValue + _AugustCurrentYearValue + _JulyCurrentYearValue + _JuneCurrentYearValue + _MayCurrentYearValue + _AprilCurrentYearValue + _MarchCurrentYearValue + _FebruaryCurrentYearValue + _JanuaryCurrentYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 1, _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 2, _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 3, _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 4, _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 5, _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 6, _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 7, _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 8, _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 9, _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 10, _OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 11, _NovemberNextYearValue + _OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue, IF ( SELECTEDVALUE ( DIM_Date[Year] ) = YEAR ( TODAY () ) + 1 && SELECTEDVALUE ( DIM_Date[Month] ) = 12, _DecemberNextYearValue + _NovemberNextYearValue + _OctoberNextYearValue + _SeptemberNextYearValue + _AugustNextYearValue + _JulyNextYearValue + _JuneNextYearValue + _MayNextYearValue + _AprilNextYearValue + _MarchNextYearValue + _FebruaryNextYearValue + _JanuaryNextYearValue ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )Show only current year in bar chart
Hi Community, I hope you are all doing great, I am working on a report where I have to show actuals and target in one chart. I successfully did this but it shows for monthyear in the dataset in the chart like in fig 1. when I put a filter to show the actual and target for the whole of the current year, it only shows from jan-mar like in fig 2. what I would like is something like in fig 3. can you help please. below is the dax code I used; Actual and Projection = VAR LastCostDay = CALCULATE( MAX(‘Usage Details’[Date]), ALL() ) VAR Actualconsumption = [New consumption YTD] VAR Result = IF( MAX(DIM_Date[Date]) > LastCostDay, [New Projections YTD], Actualconsumption ) RETURN Result I tried doing Actual and Projection = VAR LastCostDay = CALCULATE( MAX(‘Usage Details’[Date]), ALL() ) VAR Actualconsumption = [New consumption YTD] VAR Result = IF( MAX(DIM_Date[Date]) > LastCostDay, [New Projections YTD], Actualconsumption ) RETURN Calculate (Result, (DIM_Date[Year]) = Year(Today())) still it didn't work. I would be glad if you can help Thanks in advance for your helpForecasting using previous day value
Hi Team, I hope you all are doing great. I am a newbie to DAX and PBI annd currenntly working on a task to predict cost. I had to calculate the slope(which I did using the Linest function) and add this slope to the preceeding value(where preceeding value = previous day cost). so, cost prediction for today= actual value of previous day + slope and if there is no actual value present(in the case of future days), then cost prediction for future = last calculated cost prediction + slope so I wrote a dax formula, cost prediction = var _PreviousDayCost = CALCULATE([Amortized Cost(€)], PREVIOUSDAY(DIM_Date[Date])) var _predictedcost = _PreviousDayCost + [Slope] return _predictedconsumption evreything works fine until when I don't have any actual and it just returns the slope. is there a way to reference the previously calculated prediction so the right prediction is populated.like in excel where previous cell is referenced. Thanks in advance for your help and support rrent formula
