Return a result in another column based off a selected value

[JS_UNI4C]

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???

[Anonymous · Accepted solution]

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.

[JS_UNI4C]

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...!