Forum Discussion

MoCPA's avatar
MoCPA
New Member
3 years ago
Solved

Return/generate value based on date range

Hello Community,    I need to right a measure that generate value based on date range. So basically i've calendar table and data input table.    Data Input Start Date End Date Amount 1/1...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi MoCPA ,

     

    I suggest you to inactive or remove the relationship between the input table and calendar table. Then create a measure to filter your visual.

    Filter = 
VAR _START =
    SELECTEDVALUE ( 'Data Input'[Start Date] )
VAR _END =
    SELECTEDVALUE ( 'Data Input'[End Date] )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) >= _START
            && MAX ( 'Calendar'[Date] ) <= _END,
        1,
        0
    )

    Add this measure into visual level filter and set it to show items when value = 1. Result is a below.

    Best Regards,
    Rico Zhou

     

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

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi MoCPA ,

 

I suggest you to inactive or remove the relationship between the input table and calendar table. Then create a measure to filter your visual.

Filter = 
VAR _START =
    SELECTEDVALUE ( 'Data Input'[Start Date] )
VAR _END =
    SELECTEDVALUE ( 'Data Input'[End Date] )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) >= _START
            && MAX ( 'Calendar'[Date] ) <= _END,
        1,
        0
    )

Add this measure into visual level filter and set it to show items when value = 1. Result is a below.

Best Regards,
Rico Zhou

 

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