Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to make Line Chart stop mid-graph for future weeks

Hi,

 

I've made a line chart, showcasing the accumultion of received applications throughout the weeks of 2022 compared to 2021. 

 

The dataline for 2022, do not have values/data for future weeks and I'd like the line for 2022 to stop mid-graph at the current weeknumber. But, I can't figure out how to.

 

This is my current graph:

 

Measure for accumulatation current year

CurrentY(Accumulated+PrevYear) =
VAR CurrentWeek_ =
MAX ( 'Calendar'[ISOWeekNum] )
VAR StudyYear = YEAR(TODAY())
VAR AppYear = YEAR(TODAY())
VAR PrevAppYear = StudyYear-1
VAR PrevYFinal =
CALCULATE(
COUNTROWS ( 'Final_data' )
, Final_data[Year of Studystart] = StudyYear
, Final_data[Year of Application] = PrevAppYear
)
Var AccumCY=
CALCULATE (
COUNTROWS ( 'Final_data' ),
'Final_data'[ISOWeekNum] <= CurrentWeek_,
'Final_data'[Year of Studystart] = StudyYear,
'Final_data'[Year of Application]=AppYear
)
Return
AccumCY+PrevYFinal

 

This is a data sample

 

DateOfApplicationStudystartdateReceived Week NumberYear of ApplicationYear of Studystart
05-02-202204-08-2022520222022
05-02-202204-08-2022520222022
05-02-202204-08-2022520222022
02-02-202204-08-2022520222022
31-01-202204-08-2022520222022
16-12-202131-07-20225020212022
26-11-202110-01-20224720212022
25-11-202110-01-20224720212022
25-11-202110-01-20224720212022
25-11-202110-01-20224720212022
22-11-202131-07-20224720212022
22-11-202110-01-20224720212022
22-11-202110-01-20224720212022
17-11-202110-01-20224620212022
15-11-202110-01-20224620212022
14-11-202110-01-20224520212022
09-11-202110-01-20224520212022
02-11-202110-01-20224420212022
31-10-202110-01-20224320212022
29-10-202110-01-20224320212022
29-10-202110-01-20224320212022
25-10-202110-01-20224320212022
25-10-202110-01-20224320212022
25-10-202110-01-20224320212022
09-10-202110-01-20224020212022
08-10-202110-01-20224020212022
04-10-202110-01-20224020212022
02-10-202110-01-20223920212022
02-10-202110-01-20223920212022
15-09-202131-07-20223720212022
09-09-202110-01-20223620212022
23-08-202110-01-20223420212022
18-08-202110-01-20223320212022
17-08-202110-01-20223320212022
17-08-202110-01-20223320212022
06-08-202131-07-20223120212022
04-07-202110-01-20222620212022
28-05-202110-01-20222120212022
26-05-202110-01-20222120212022
20-05-202110-01-20222020212022
25-03-202110-01-20221220212022
18-02-202131-07-2022720212022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202204-08-2022620222022
07-02-202230-03-2022620222022
07-02-202204-08-2022620222022
07-02-202230-03-2022620222022
07-02-202208-08-2022620222022
04-02-202208-08-2022520222022
07-02-202208-08-2022620222022
07-02-202208-08-2022620222022
07-02-202208-08-2022620222022
06-02-202230-03-2022520222022
06-02-202204-08-2022520222022
06-02-202230-03-2022520222022
06-02-202204-08-2022520222022

 

 

Thanks in advance 😃

 

  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    Hi Anonymous 

     

    What you want to do is compare the current (or possibly last) week number available in the data to the week number in the axis - use MAX(WeekNumber).  If the week number in the axis > current/last available in data then BLANK() otherwise show value.

     

    Hope this helps!

3 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    Hi Anonymous 

     

    What you want to do is compare the current (or possibly last) week number available in the data to the week number in the axis - use MAX(WeekNumber).  If the week number in the axis > current/last available in data then BLANK() otherwise show value.

     

    Hope this helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    How does 'Final_data'[ISOWeekNum] come from?

    Does your calendar table have a relationship with the main table?
    Your image is not clear enough, I can't see the specific content in Fields.

    When publishing, you can enlarge the size of your image.

     

    Best Regards,

    Stephen Tao

     

    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
      4 years ago

      Hi, 

       

      'Final_data'[ISOWeekNum]= if(and(Final_data[Received Week Number]>51,month(Final_data[DateOfApplication])=1),1,Final_data[Received Week Number])
       
      to handle whenever week 52 or 53 ends in a new year.
       
      I'have not linked the calendar table directedly to the main table, but I'm using the calendar as my date table.
       
      I have suceeded in making the line chart stop mid graph implementing the following code
       
      CurrentY(Accumulated+PrevYear) =
      IF(MAX('Calendar'[ISOWeekNum])>=WEEKNUM(MAX(Final_data[DateOfApplication]),2),Blank(),
      VAR CurrentWeek_ =
      MAX ( 'Calendar'[ISOWeekNum] )
      VAR StudyYear = YEAR(TODAY())
      VAR AppYear = YEAR(TODAY())
      VAR PrevAppYear = StudyYear-1
      VAR PrevYFinal =
      CALCULATE(
      COUNTROWS ( 'Final_data' )
      , Final_data[Year of Studystart] = StudyYear
      , Final_data[Year of Application] = PrevAppYear
      )
      Var AccumCY=
      CALCULATE (
      COUNTROWS ( 'Final_data' ),
      'Final_data'[ISOWeekNum] <= CurrentWeek_,
      'Final_data'[Year of Studystart] = StudyYear,
      'Final_data'[Year of Application]=AppYear
      )
      Return
      AccumCY+PrevYFinal)