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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nas211
Regular Visitor

Datesbetween

Hi guys, I'm néw to writing Dax queries and I'm currently stuck trying to use the "Datesbetween" feature to write queries with multiple criteria. I'm trying to have a stacked trend lines with multiple years. I want the lines to be overlapping in a combined trend form. 

The query bellow is not working. I'll appreciate inputs at this point. 

 

E.g

 

TrendYears = CALCULATE(SUM(merged_data[Workinghours]), Datesbetween (merged_data[Date], date(2019,1,1), date(2019,12,31)),

Datesbetween (merged_data[Date], date(2020,1,1), date(2020,12,31)),

Datesbetween (merged_data[Date], date(2021,1,1), date(2021,12,31)),

Datesbetween (merged_data[Date], date(2022,1,1), date(2022, 5, 25)))

 

2 ACCEPTED SOLUTIONS
Adescrit
Impactful Individual
Impactful Individual

To produce a line chart that shows multiple years worth of data I would suggest the following steps:

  1. Create a date dimension table (if you don't already have one), with the columns Date, Month and Year.
  2. Create a relationship between the date dimension table and the merged_data[Date] column.
  3. Create a measure for Working Hours = SUM( merged_data[WorkingHours])
  4. Add a line chart visual to your report
  5. Add the Working Hours measure to the Values section of the line chart
  6. Add the Month column (from the date dimension table) to the x-axis of the line chart
  7. Add the Year column from the date dimension table to the Legend section of the line chart.

Did I answer your question? Mark my post as a solution!
My LinkedIn

View solution in original post

Adescrit
Impactful Individual
Impactful Individual

Hi @Nas211 

 

In your date dimension table, create the following two columns:

 

Month Number = MONTH( 'Calendar'[Date] )

Month Name Short = FORMAT( 'Calendar'[Date], "MMM" )

 

In your line chart visual, add Month Name Short to the x-axis. (If you want the full month name, e.g. January rather than Jan, add an extra "m" into the FORMAT function.)

 

By default the months will be sorted alphabetically. To sort them in calendar order, go to Data view, select the Month Name Short from the fields list, and at the top of the page you'll see "Sort by Column". Click on that and then select "Month Number". This tells Power BI to sort the month name column numerically in order of the month numbers.


Did I answer your question? Mark my post as a solution!
My LinkedIn

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

Hi @Nas211 ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Adescrit
Impactful Individual
Impactful Individual

To produce a line chart that shows multiple years worth of data I would suggest the following steps:

  1. Create a date dimension table (if you don't already have one), with the columns Date, Month and Year.
  2. Create a relationship between the date dimension table and the merged_data[Date] column.
  3. Create a measure for Working Hours = SUM( merged_data[WorkingHours])
  4. Add a line chart visual to your report
  5. Add the Working Hours measure to the Values section of the line chart
  6. Add the Month column (from the date dimension table) to the x-axis of the line chart
  7. Add the Year column from the date dimension table to the Legend section of the line chart.

Did I answer your question? Mark my post as a solution!
My LinkedIn

Thanks for the swift response. I was wondering if the Year column from the date dimension table to the Legend section of the line chart will show each individual year trendline (2019, 2020,2021 and 2022); that is a total of 4 lines? 

Adescrit
Impactful Individual
Impactful Individual

Hi @Nas211 ,

 

Yes that's exactly what it should do.

 

I think the DAX in your initial post will not work as your definingmultiple conflicting filters. That calculation will essentially try to return any rows in the tablethat satisfies ALL of the DATESBETWEEN functions at the same time (which isn't possible). So hopefully by using some data modelling instead of DAX you can produce a simpler solution.


Did I answer your question? Mark my post as a solution!
My LinkedIn

03F92689-DD01-408A-B128-D3F7141FF937.jpeg

 Hi, your initial suggestion works great! But I'd like to have these lines stacked. The X axis should only have Single month names like Jan2019 to Jul 2022.  Here, the months for each year shows which prevents the trendlines from stacking. 

What are your thoughts?

 

Thanks.

Adescrit
Impactful Individual
Impactful Individual

Hi @Nas211 

 

In your date dimension table, create the following two columns:

 

Month Number = MONTH( 'Calendar'[Date] )

Month Name Short = FORMAT( 'Calendar'[Date], "MMM" )

 

In your line chart visual, add Month Name Short to the x-axis. (If you want the full month name, e.g. January rather than Jan, add an extra "m" into the FORMAT function.)

 

By default the months will be sorted alphabetically. To sort them in calendar order, go to Data view, select the Month Name Short from the fields list, and at the top of the page you'll see "Sort by Column". Click on that and then select "Month Number". This tells Power BI to sort the month name column numerically in order of the month numbers.


Did I answer your question? Mark my post as a solution!
My LinkedIn

Thanks. It did work. I appreciate the the inputs. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors