Forum Discussion

JS_UNI4C's avatar
JS_UNI4C
Helper I
2 years ago
Solved

Return a result in another column based off a selected value

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's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JS_UNI4C
      Helper I
      2 years ago

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