Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
norken20
Helper I
Helper I

Sorting of Date in Line Graph and DAX for adding rows

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:

DateValueWhat I want to reflect in Graph
Sep-19200200
Oct-19300500
Nov-19300800
Dec-194001200
 
 

SAMPLE.PNG

2 REPLIES 2
amitchandak
Super User
Super User

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/

 

Sort_by_column.png

 

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

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.