Return a result in another column based off a selected value

JS_UNI4C — Thu, 06 Jun 2024 12:34:03 GMT

Hi all,

I am hoping there is a guru on here who will enlighten me as I am hitting a wall on what I thought would be straight forward.

I am creating a sales dashboard and want the main page to be dynamic base don the date selected in a slicer e.g:

YTD: Results for the year up to the date selected

MTD: Results for the month of the data selected i.e. if a user selected 1/10/20 June 2024, the month is June 2024

DAY: Results for the day selected

YTD and MTD are proving tricky. I can calcuate them individually no problem but it is the "making them dynamic part" that is confusing me.

I was hoping to achieve this with the "SELECTEDVALUE" function to return a specific column from my calendar table which I would then use in my measure, but it is the bold and underlined part that is completely stumping me.

In my calendar table, I have a column called "MonthYearNum" which is simply the year and the month number so June 2023 is 202306. This is the column I want returned based on the user selection in the date slicer.

I was then hoping to use that value in a revenue measure along the lines of:

Revenue MTD = CALCULATE([SalesSum],FILTER('Calendar',

'Calendar'[Date] = [Selected month]))

[SalesSum] being my measure used which is calculating the sum of all sales but will be filtered down based on user selection.

[Selected month] being the MonthYearNum based off the date selection by the user.

My calendar is connected to my source data via the date in both tables.

For the YTD value, I was simply going to filter it by

Revenue YTD = CALCULATE([SalesSum],FILTER('Calendar',

'Calendar'[Date] <= MAX([Selected month])

'Calendar'[Date] = MAX([Selected year])))

If this won't work, any help would be appreciated to get a solution.

Can anyone help me with this???

Re: Return a result in another column based off a selected value

Anonymous — Fri, 07 Jun 2024 06:58:56 GMT

Hi JS_UNI4C ,

For combining dynamic metrics with slicers, try the following formula:

SelectedMeasureType = SELECTEDVALUE('MeasureType'[Measure], "YTD")

Dynamic Revenue = SWITCH( [SelectedMeasureType], "YTD", [Revenue YTD], "MTD", [Revenue MTD], "DAY", [Revenue DAY] )

Revenue DAY = CALCULATE( [SalesSum], FILTER( 'Calendar', 'Calendar'[Date] = SELECTEDVALUE('Calendar'[Date]) ) )

Best Regards,
Adamk Kong

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

Re: Return a result in another column based off a selected value

JS_UNI4C — Tue, 11 Jun 2024 12:42:04 GMT

Hi,

Many thanks for the response and sorry for the lack of response on my part. Been a crazy few days but it is back to Power BI now so I will be giving this a go today! Hoping it works...!

topic Return a result in another column based off a selected value in DAX Commands and Tips

Return a result in another column based off a selected value

Re: Return a result in another column based off a selected value

Re: Return a result in another column based off a selected value