Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |