Forum Discussion
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 amount of import & export have been created. An attempt was made using the YTD cumulative data, plotted year & month column derived from the original date column. However, this resulted in a graph with a non-cumulative value.
What would be the best way to plot a graph to compare the cumulative YTD data year to year? Any comments or guidance would be deeply appreciated.
---
The .pbix file can be downloaded from here:
The following resources have been referred to:
- Compare cumulative data between years https://community.powerbi.com/t5/Desktop/Compare-cumulative-data-between-years/m-p/112480
- DAX for Power BI - MTD/YTD vs Previous Years https://youtu.be/t4kd-QY6Rh0
The following measures were created as an attempt to follow the youtube reference:
YTD_import_Dlr =
TOTALYTD(SUM(data_customs_import_export[impDlr]), data_customs_import_export[date])LY_YTD_importDlr =
CALCULATE(
[YTD_import_Dlr],
SAMEPERIODLASTYEAR(data_customs_import_export[date])
)AllYears_YTD_importDlr =
VAR Yearsback = YEAR(TODAY())-SELECTEDVALUE(data_customs_import_export[year])
RETURN
CALCULATE(
data_customs_import_export[YTD_import_Dlr],
DATEADD(data_customs_import_export[date], -Yearsback, YEAR)
)
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.
3 Replies
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.Thank you amitchandak,
Using a seperate date table definitely helped for this issueHi 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.
