Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kj19
New Member

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

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.

 

Kj19_0-1717831013162.png

 

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 %
)

1 ACCEPTED SOLUTION
v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1718160691819.png

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.

vlinyulumsft_1-1718160943453.png

vlinyulumsft_2-1718160951320.png

 

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.


 

View solution in original post

1 REPLY 1
v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1718160691819.png

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.

vlinyulumsft_1-1718160943453.png

vlinyulumsft_2-1718160951320.png

 

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.


 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.