Sorting of Date in Line Graph and DAX for adding rows

[norken20]

Hi All,

I'm having a hard time fixing this scenario in Power BI. I'm trying to create Line graph that will show the Target Goals.

My first problem is that my dates are not arranged as reflected in my column from Sep-19 to Aug-20.

Second is what is the best way to calculate sum of each row using Dax. I want to reflect the column is the sum of rows. Sep-19 will reflect the orginal value but for the next month it will reflect current month value plus the previous month. How can I do it using DAX?

For example:

Date	Value	What I want to reflect in Graph
Sep-19	200	200
Oct-19	300	500
Nov-19	300	800
Dec-19	400	1200

[amitchandak]

Hope you have date dimension table. If not create one.

To sort Month-Year you need to create a column

sort month-year = format(date,"YYYYMM") and use this for sorting.

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Now if you want cummalative till yeat end use datesytd or total ytd

else use over all

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

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

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[norken20]

Thank for the answer on creating line graph. I'm just a bit confused on Dax as it seems not working at all. Will you be able to help? Thanks!

@amitchandak