Help in converting a Spotfire calculated column in Power BI

[vivek_babu]

Hi,

I am trying to migrate a spotfire report to power bi. I got stuck while converting below calculated column in spotfire to power bi. I pasted the formula which needs to be converted in power bi as a calculated column. The issue here is that it passes the parameter values for year and month from the filter based on user selection and then calculates dynamically. But in power bi we cant use selectedvalue funtion while creating calculated columns so please provide some help in converting this formula in power bi.

Spotfire calculated column: Here  ${Year} and '${MonthNew}' are parameter values based on user selection 

(Avg(case when Year([Reporting Date]) = ${Year} and BinByDateTime([Reporting Date],'Month',0) = '${MonthNew}'
then Sum(If(Lower(case when [Agent Status] is null then "na" else [Agent Status] end) in ("alive","available"),1,0)) /
Count([Reporting Date]) end))
-
(
case when '${MonthNew}' = 'Jan' then
Sum(case when (Year([Reporting Date])=${Year}-1) and (BinByDateTime([Reporting Date],"Month",0) = 'Dec')
and (Lower(case when [Agent Status] is null then "na" else [Agent Status] end) in ("alive","available")) then
1 else 0 end)
/
Sum(case when (Year([Reporting Date])=${Year}-1) and (BinByDateTime([Reporting Date],"Month",0) = 'Dec') then 1 else 0 end)
else
Sum(case when (Year([Reporting Date])=${Year}) and (BinByDateTime([Reporting Date],"Month",0) =
case when '${MonthNew}' = 'Feb' then 'Jan'
when '${MonthNew}' = 'Mar' then 'Feb'
when '${MonthNew}' = 'Apr' then 'Mar'
when '${MonthNew}' = 'May' then 'Apr'
when '${MonthNew}' = 'Jun' then 'May'
when '${MonthNew}' = 'Jul' then 'Jun'
when '${MonthNew}' = 'Aug' then 'Jul'
when '${MonthNew}' = 'Sep' then 'Aug'
when '${MonthNew}' = 'Oct' then 'Sep'
when '${MonthNew}' = 'Nov' then 'Oct'
when '${MonthNew}' = 'Dec' then 'Nov'
end
)
and (Lower(case when [Agent Status] is null then "na" else [Agent Status] end) in ("alive","available")) then
1 else 0 end)
/
Sum(case when (Year([Reporting Date])=${Year}) and (BinByDateTime([Reporting Date],"Month",0) = case when '${MonthNew}' = 'Feb' then 'Jan'
when '${MonthNew}' = 'Mar' then 'Feb'
when '${MonthNew}' = 'Apr' then 'Mar'
when '${MonthNew}' = 'May' then 'Apr'
when '${MonthNew}' = 'Jun' then 'May'
when '${MonthNew}' = 'Jul' then 'Jun'
when '${MonthNew}' = 'Aug' then 'Jul'
when '${MonthNew}' = 'Sep' then 'Aug'
when '${MonthNew}' = 'Oct' then 'Sep'
when '${MonthNew}' = 'Nov' then 'Oct'
when '${MonthNew}' = 'Dec' then 'Nov'
end) then 1 else 0 end)
end
)

@lbendlin @rajendraongole1 @Kedar_Pande @Ritaf1983 @SamWiseOwl @vojtechsima @FreemanZ @shafiz_p @johnt75 

Your help would be much appreciated 

Regards

Vivek N

[Anonymous]

Hi @vivek_babu 

As vojtechsima mentioned in his reply, please provide some sample data for us to fully understand your question.

Performing dynamic calculations suggests that you use measures instead of calculated columns. Please try the following possible solution to see if can solve the problem.

1. Create a parameter table with the year and month.

Parameters = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('YourDataTable'[Year]),
        DISTINCT('YourDataTable'[Month])
    ),
    "MonthNew", 'YourDataTable'[Month]
)

2. Create a measure for dynamic calculation.

AvgAliveAvailable = 
VAR SelectedYear = SELECTEDVALUE(Parameters[Year])
VAR SelectedMonth = SELECTEDVALUE(Parameters[MonthNew])
VAR PreviousMonth = 
    SWITCH(
        SelectedMonth,
        "Feb", "Jan",
        "Mar", "Feb",
        "Apr", "Mar",
        "May", "Apr",
        "Jun", "May",
        "Jul", "Jun",
        "Aug", "Jul",
        "Sep", "Aug",
        "Oct", "Sep",
        "Nov", "Oct",
        "Dec", "Nov",
        "Jan", "Dec"
    )
VAR CurrentMonthData = 
    FILTER(
        'YourDataTable',
        YEAR('YourDataTable'[Reporting Date]) = SelectedYear &&
        FORMAT('YourDataTable'[Reporting Date], "MMM") = SelectedMonth &&
        LOWER('YourDataTable'[Agent Status]) IN {"alive", "available"}
    )
VAR PreviousMonthData = 
    FILTER(
        'YourDataTable',
        YEAR('YourDataTable'[Reporting Date]) = IF(SelectedMonth = "Jan", SelectedYear - 1, SelectedYear) &&
        FORMAT('YourDataTable'[Reporting Date], "MMM") = PreviousMonth &&
        LOWER('YourDataTable'[Agent Status]) IN {"alive", "available"}
    )
RETURN
    AVERAGEX(CurrentMonthData, 1) - 
    DIVIDE(
        COUNTROWS(PreviousMonthData),
        COUNTROWS(
            FILTER(
                'YourDataTable',
                YEAR('YourDataTable'[Reporting Date]) = IF(SelectedMonth = "Jan", SelectedYear - 1, SelectedYear) &&
                FORMAT('YourDataTable'[Reporting Date], "MMM") = PreviousMonth
            )
        )
    )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vivek_babu]

Hi @Anonymous ,

Thanks for providing your solution. I will try and let you know 

Regards

Vivek N

[vivek_babu]

Hi @vojtechsima 

Thanks for your reply. I tried to create it as measure but because i cant use columns to compare values in if or switch conditions i am getting stuck. 
example:
case when Year([Reporting Date]) = ${Year} and  Month([Reporting Date]) = '${MonthNew}' then 1 else 0 end 

how to acheive this using a measure? [Reporting_Date] is a column in the table. I need to replace ${Year} and ${MonthNew} using selectedvalue fucntion to compare the [Reporting_Date]. 

Can you help me with this part please?

@lbendlin @amitchandak @Ritaf1983 @SamWiseOwl @johnt75 @Kedar_Pande @shafiz_p @FreemanZ @FarhanJeelani 

Regards

Vivek N

[vojtechsima]

Hello, @vivek_babu 

This is probably too long for me to ball it.

Can you add sample data and desired outcome with some business logic for this?

Also I wonder why you need to calculate as Caluclated Column when you need a users input?
Can't you write it as a measure, where you actually got the interactions from the SELECTEDVALUE function?