DAX help - To get percentage for the selected value in Month slicer and apply for future months

[Kj19]

Hi,

I am new to Power BI and learning DAX.

I am trying to get Screen target % for the selected month Mar 24 (value is 0.92) and use this value for months which are not completed yet, in my case it is Jun 24. 

When I use the formula (Target * Screen target %) in a measure, Screen target % for Jun 24 (0.85) is used instead of 0.92 for the selected month Mar 24. I used a variable in the measure to calculate Screen target % for Mar 24 ie 0.92, but the final value used was still 0.85*396,792 = 337,674.

Can I please get help in solving this issue? Thank you for your help.

DAX code:

Screens Actual Forecast Cumulative =

VAR selectedFYStartDate =
SELECTEDVALUE ( DimCalendar[FY_START_DATE] )
VAR selectedMonthEndDate =
SELECTEDVALUE ( 'DimCalendar'[CALENDAR_MONTH_ENDING_DATE] )
VAR selectedMonthStartDate =
EOMONTH ( selectedMonthEndDate, -1 ) + 1
VAR selectedMonthCheck =
SELECTEDVALUE ( DimCalendar[Before Completed Month] )
VAR screensFYTD =
CALCULATE (
[Screens All ages],
ALL ( DimCalendar ),
DimCalendar[DIM_DATE_SK] >= selectedFYStartDate
&& DimCalendar[DIM_DATE_SK] <= selectedMonthEndDate
)
VAR targetFYTD =
CALCULATE (
[Screens Target All ages],
ALL ( DimCalendar ),
DimCalendar[DIM_DATE_SK] >= selectedFYStartDate
&& DimCalendar[DIM_DATE_SK] <= selectedMonthEndDate
)
VAR screensTargetPercentage =
DIVIDE ( screensFYTD, targetFYTD ) -- Screen target % for selected month

RETURN
IF (

selectedMonthCheck = 1, -- Check if it a completed months in the financial year,
[Screens cumulative All ages Base FY], -- Actuals
[Target cumulative All ages Base FY] * screensTargetPercentage -- Target*Screen target %
)

[Anonymous · Accepted solution]

Hi,@Kj19

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated table references:

Table = SELECTCOLUMNS('DimCalendar',"date",'DimCalendar'[Month])

Then use this column as a slicer.

3. Below are the measure I've created for your needs:

Measure = 
VAR select22=SELECTEDVALUE('Table'[date])
RETURN IF(SUM('DimCalendar'[Month])=DATE(2024,6,24),SUM('DimCalendar'[Target])*CALCULATE(MAX('DimCalendar'[Screens Target%]),'DimCalendar'[Month]=select22),SUM('DimCalendar'[Actual]))

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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