Forum Discussion

Chetcork's avatar
Chetcork
Frequent Visitor
4 years ago
Solved

Display future dates

Hi everyone,

 

I'm trying to create line chart with two lines one for current data and one line for last year data. I have measures worked out and aswell date table. Date table is created using startdate and enddate from column of queried from sql server, data is refresed everyweek in sql server. Now back to the line chart, im trying to create line chart that will show all the months from 2022 and line representing current yearr data and as well last year data. I assume that future dates in datetable are necessary, but if you guys have any idea feel free to write. Please take a look on low quality picture below

 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Chetcork

     

    Your assumption is correct that future dates in datetable are necessary. At least the datetable should have dates till the end of 2022. Then you can use measures like below and put them into the line chart. 

    This Year = CALCULATE(SUM('Table'[Value]),'Date'[Year]=2022)
    Last Year = CALCULATE(SUM('Table'[Value]),'Date'[Year]=2021)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Chetcork ,
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Chetcork's avatar
      Chetcork
      Frequent Visitor
      4 years ago

      Hi,

       

      I cannot share pbix file, but we can work on below sample data. I want line chart displaying only year 2022 from january to december that contains last year data and current data. Dates in my datetable are queried from sql therfore max date is forced on refresh of dataset

       

      01.01.202125
      01.02.202150
      01.03.202113
      01.04.202112
      01.05.202167
      01.06.202190
      01.07.202132
      01.08.2021120
      01.09.202135
      01.10.202111
      01.11.202112
      01.12.202190
      01.01.202287
      01.02.202228
      01.03.202212
      01.04.202212
      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        4 years ago

        Hi Chetcork

         

        Your assumption is correct that future dates in datetable are necessary. At least the datetable should have dates till the end of 2022. Then you can use measures like below and put them into the line chart. 

        This Year = CALCULATE(SUM('Table'[Value]),'Date'[Year]=2022)
        Last Year = CALCULATE(SUM('Table'[Value]),'Date'[Year]=2021)

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.