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 DateEnd DateAmount
1/1/202012/31/2021100

 

So the requirement is to put this on line chart that has periods from calendar table on x axes and we want a line shows the amount the Amount on every month within that period. 

 

Any ideas other that building a table to spread the amounts of rows? 

 

 

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

     

     

2 Replies