Forum Discussion

ohikendoit's avatar
ohikendoit
New Member
4 years ago
Solved

Line graph plotting to compare the cumulative YTD data

Hello all,   I've come across an issue while trying to plot a graph that can compare cumulative data between years.     As shown from the image, the measured columns of the YTD cumulative ...
General Comment
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi ohikendoit ,

     

    Please reference the date column in the ref_calendar table in the time intelligence functions, and use the year and month column of the ref_calendar table as the legend and axis of the line graph.

     

    YTD_TEST_import_Dlr = 
CALCULATE (
    SUM ( data_customs_import_export[impDlr] ),
    DATESYTD ( ref_calendar[Date] )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
4 years ago

ohikendoit , for last year, you measure should work with date table or try like the example

 

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • ohikendoit's avatar
    ohikendoit
    New Member
    4 years ago

    Thank you amitchandak,

    Using a seperate date table definitely helped for this issue

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      Hi ohikendoit ,

       

      Please reference the date column in the ref_calendar table in the time intelligence functions, and use the year and month column of the ref_calendar table as the legend and axis of the line graph.

       

      YTD_TEST_import_Dlr = 
CALCULATE (
    SUM ( data_customs_import_export[impDlr] ),
    DATESYTD ( ref_calendar[Date] )
)

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.