need help for creating a custom date horizon

[SeGr]

Hi team,

I have a request to create a custom date horizon. What does this mean? I am working with forecast snapshots. For instance, if I select this week's snapshot, I will see the forecast for the next 26 weeks.

What business needs is to modify the weeks covered in the visual. They don't need to see the full 26 weeks, but only the next 13 weeks or 17 weeks.

Using Zoom Slider does not work as the column used for Weeks dimension is Text.

What I thought was to create a ranking in the visual based on the snapshot. In other words, create a ranking for all 26 weeks which are forecasted (0-25) and use a parameter to let the user decide how many weeks should be in the visual.

This is the code that I wrote, but doesn't work. What am I missing? Also, if you have any other way of doing this, i'm open to suggestions.

Calendar WIP =

var _Calendar =

calculatetable(

    ADDCOLUMNS(

        SUMMARIZE(

            filter('Calendar','Calendar'[Calendar_Date]>= date(2021,01,01)),

            'Calendar'[Calendar_Date], --i'm adding the Date column to sort the YearAndWeek properly in the ranking

            'Calendar'[Calendar_YearAndWeek]

        ),

         "@Rank",

        RANKX(all('Calendar'),'Calendar'[Calendar_Date],,ASC,Dense)

),

removefilters('Calendar'[Calendar_YearAndWeek])) -- adding this to remove the filter context from the visual

var _Rank=

//I'm summarizing the YearAndWeek column and rank it based on the new @Rank column from the prev table. This way I can //remove the Date column and be left with only YearAndWeek sorted properly

ADDCOLUMNS(

    summarize(

        _Calendar,

        [Calendar_YearAndWeek],

        [@Rank]

        ),

    "@New Rank",

    RANKX(_Calendar,[@Rank],,ASC,Dense)

)

return

minx(_Rank,[@New Rank])

But it doesn't work. I get a 1 for each row.

In the table I only have YearAndWeek column and the Calendar Horizon measure.

Can you please help?

[SeGr]

I found a more efficient way of doing this - define an upper and lower bound and calculate the measure to be between those 2. Make the upper bound to be dependent of a parameter to make it dynamic.

[v-xiaotang · Accepted solution]

Hi @SeGr 

Thanks for reaching out to us.

you can try this measure

filtermeasure = 
var _week1=MINX(ALLSELECTED(weeks),[value])
var _week2=MAXX(ALLSELECTED(weeks),[value])
var _date1= MINX(ALLSELECTED('calendar'),[Date])
var _date2= MAXX(ALLSELECTED('calendar'),[Date])
var _date3= _date1+_week1*7
var _date4= _date2+_week2*7
return 
IF(MIN('Table'[date]) >=_date3 && MIN('Table'[date])<=_date4,1,0)

Best Regards,

Community Support Team _Tang

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