Forum Discussion

msagradian's avatar
msagradian
Regular Visitor
4 years ago
Solved

Dynamic MTD/YTD selector

Dear Power BI Community,   I believe I have an interesting problem for you, or at least the one that I couldn't find a solution for in a several days so hope you could point me in the right directi...
Need Help
Show and Tell
  • msagradian's avatar
    msagradian
    4 years ago

    Thank you for your help and time v-zhangti ,

     

    This is an interesting way to approach this, I might give it a try. I think I got it working for me through a mix of solutions (posted it here: (1) Dynamic MTD/YTD selector : PowerBI (reddit.com) ).

     

    Basically, what I did was:

    1. Created a 'copy' of the 'Calendar' table called 'Date selection' that serves as an 'extraction' tool for the range of dates I'm interested in. For example, dates from 1JUL2021 to 15MAY2022. (Initially, this table was disconnected from 'Calendar' but I connected it later to filter out unnecessary dates from the visuals).

    2. Updated the measures in my Calculation Group ('Time intelligence new'). Below is the example for MTD measure.

    This is the changes to my calculation items (example for MTD):

     

    VAR selected_date = MAX ( 'Date selection'[Date] )

VAR min_date = DATE ( YEAR ( selected_date ), MONTH ( selected_date ), 1 )

VAR max_date = selected_date

RETURN IF ( OR ( MAX ( 'Calendar'[Date] ) > max_date, MAX ( 'Calendar'[Date] ) < min_date ), BLANK (),

CALCULATE ( SELECTEDMEASURE (),
	FILTER (ALL ( 'Calendar' ), 
	'Calendar'[Date] >= min_date && 'Calendar'[Date] <= max_date && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) 
		) 
)
)

     

     

    Seems to be working, but if you have any feedback (not really sure why this filtering logic works) or a better solution - keen to hear.

    Thank you, Martin

     

     

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, msagradian 

 

I have an easy way to do this, but it may change your current view. But it is not possible in your matrix, it is possible in Table.

Measure = 
IF (
    SELECTEDVALUE ( 'Time intelligence new'[Name] ) = "MTD new"
        && [Cumulative return] <> BLANK (),
    1,
    IF (
        SELECTEDVALUE ( 'Time intelligence new'[Name] ) = "YTD new"
            && [Cumulative return] <> BLANK (),
        1,
        0
    )
)

I tried to set Measure equal to 1 in your original matrix, but it works in the table view. Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

msagradian's avatar
msagradian
Regular Visitor
4 years ago

Thank you for your help and time v-zhangti ,

 

This is an interesting way to approach this, I might give it a try. I think I got it working for me through a mix of solutions (posted it here: (1) Dynamic MTD/YTD selector : PowerBI (reddit.com) ).

 

Basically, what I did was:

  1. Created a 'copy' of the 'Calendar' table called 'Date selection' that serves as an 'extraction' tool for the range of dates I'm interested in. For example, dates from 1JUL2021 to 15MAY2022. (Initially, this table was disconnected from 'Calendar' but I connected it later to filter out unnecessary dates from the visuals).

  2. Updated the measures in my Calculation Group ('Time intelligence new'). Below is the example for MTD measure.

This is the changes to my calculation items (example for MTD):

 

VAR selected_date = MAX ( 'Date selection'[Date] )

VAR min_date = DATE ( YEAR ( selected_date ), MONTH ( selected_date ), 1 )

VAR max_date = selected_date

RETURN IF ( OR ( MAX ( 'Calendar'[Date] ) > max_date, MAX ( 'Calendar'[Date] ) < min_date ), BLANK (),

CALCULATE ( SELECTEDMEASURE (),
	FILTER (ALL ( 'Calendar' ), 
	'Calendar'[Date] >= min_date && 'Calendar'[Date] <= max_date && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) 
		) 
)
)

 

 

Seems to be working, but if you have any feedback (not really sure why this filtering logic works) or a better solution - keen to hear.

Thank you, Martin