Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
AsNa_92
Helper I
Helper I

Specific series of dates in line chart based on date slicer

‎05-12-2024 10:13 PM

Hi Guys

 

I want the when the user select a date a series of months shows in line chart based on selected date as below:

 

111.png

 

I tried start date as selected value and end date as selected value - 365 but it didn't work.

could anyone help please.

Message 1 of 11
239 Views
0
1 ACCEPTED SOLUTION
v-jianpeng-msft
Community Support
Community Support
In response to AsNa_92

‎05-20-2024 12:12 AM

Hi, @AsNa_92 

The reason why this date is repeated is that I changed the date format to MMMM-YYYY for this date table, which is actually every day, as shown in the figure below:

vjianpengmsft_0-1716187050373.png

To eliminate this duplication, we need to create a new column in the date table, Month&Year:

Moth&Year = FORMAT('Date table'[Date],"MMM-YY")

vjianpengmsft_1-1716187166564.png

Place this column in the slicer:

vjianpengmsft_2-1716187846937.png

Use the following measure2:

Measure2 = 
VAR _seleted_day = CALCULATE(MAX('Date table'[Date]),'Date table'[Moth&Year]=SELECTEDVALUE('Date table'[Moth&Year]))
VAR _seleted_day_365 = _seleted_day-365
VAR _filter_value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[DATE]>=_seleted_day_365&&'Table'[DATE]<=_seleted_day))
RETURN IF(ISFILTERED('Date table'[Moth&Year]),_filter_value,SUM('Table'[Value]))

Here are the results:

vjianpengmsft_3-1716188372857.png

The reason why Feb2024, the linechart shows Jan2024 is that choosing the second Feb2024 is actually choosing the date as shown in the image below:

vjianpengmsft_4-1716188931547.png

vjianpengmsft_5-1716188957218.png

You can correct this with the method I just mentioned:

vjianpengmsft_6-1716189034953.png

I've provided the PBIX file used this time below.

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

 

 

 

 

 

View solution in original post

Specific series of dates in line chart based on date slicer1.pbix
Message 10 of 11
71 Views
0
10 REPLIES 10
v-jianpeng-msft
Community Support
Community Support

‎05-19-2024 08:15 PM

Thanks @Sergii24 and @vanessafvg 

Hi, @AsNa_92 

Based on your description and the data provided, you want to use the year, month, and month selected by the slicer as the end date of 365 days. The selected month and year are used as the start date. First of all, you need to generate a calculation table of the date column based on your date column, this table does not need to establish a relationship with the original table, this table is mainly used for slicers and filtering data.

Date table = CALENDAR(MIN('Table'[DATE]),MAX('Table'[DATE]))

vjianpengmsft_0-1716174494770.png

vjianpengmsft_1-1716174509288.png

vjianpengmsft_2-1716174555020.png

You'll need to create the following measure and place it in your line chart:

Measure = 
VAR _seleted_day = SELECTEDVALUE('Date table'[Date])
VAR _seleted_day_365 = DATEADD('Date table'[Date],-12,MONTH)
VAR _filter_value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[DATE]>=_seleted_day_365&&'Table'[DATE]<=_seleted_day))
RETURN IF(ISFILTERED('Date table'[Date]),_filter_value,SUM('Table'[Value]))

vjianpengmsft_3-1716174647161.png

Here are the results:

vjianpengmsft_4-1716174730844.png

vjianpengmsft_5-1716174772918.png

If you want 2024 to appear at the beginning, you can adjust it like this:

vjianpengmsft_6-1716174850906.png

I provide the PBIX file used this time below.

 

 

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

Specific series of dates in line chart based on date slicer.pbix
Message 8 of 11
88 Views
0
AsNa_92
Helper I
Helper I
In response to v-jianpeng-msft

‎05-19-2024 11:21 PM

Hi @v-jianpeng-msft 

Thanks for the reply and it works, but why the date is duplicated? ( I want the slicer to be single select)

and If i selected another date of Feb2024, the linechart shows Jan2024:

AsNa_92_0-1716186051065.png

 

Message 9 of 11
74 Views
0
v-jianpeng-msft
Community Support
Community Support
In response to AsNa_92

‎05-20-2024 12:12 AM

Hi, @AsNa_92 

The reason why this date is repeated is that I changed the date format to MMMM-YYYY for this date table, which is actually every day, as shown in the figure below:

vjianpengmsft_0-1716187050373.png

To eliminate this duplication, we need to create a new column in the date table, Month&Year:

Moth&Year = FORMAT('Date table'[Date],"MMM-YY")

vjianpengmsft_1-1716187166564.png

Place this column in the slicer:

vjianpengmsft_2-1716187846937.png

Use the following measure2:

Measure2 = 
VAR _seleted_day = CALCULATE(MAX('Date table'[Date]),'Date table'[Moth&Year]=SELECTEDVALUE('Date table'[Moth&Year]))
VAR _seleted_day_365 = _seleted_day-365
VAR _filter_value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[DATE]>=_seleted_day_365&&'Table'[DATE]<=_seleted_day))
RETURN IF(ISFILTERED('Date table'[Moth&Year]),_filter_value,SUM('Table'[Value]))

Here are the results:

vjianpengmsft_3-1716188372857.png

The reason why Feb2024, the linechart shows Jan2024 is that choosing the second Feb2024 is actually choosing the date as shown in the image below:

vjianpengmsft_4-1716188931547.png

vjianpengmsft_5-1716188957218.png

You can correct this with the method I just mentioned:

vjianpengmsft_6-1716189034953.png

I've provided the PBIX file used this time below.

 

 

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

 

 

 

 

 

Specific series of dates in line chart based on date slicer1.pbix
Message 10 of 11
72 Views
0
AsNa_92
Helper I
Helper I
In response to v-jianpeng-msft

‎05-20-2024 10:37 PM

Hi @v-jianpeng-msft 

Thank you so much for the help! I really appreciate it 😁

Message 11 of 11
43 Views
0
AsNa_92
Helper I
Helper I

‎05-16-2024 09:58 PM

Hi @amitchandak 
could you please help me on this?

Message 7 of 11
123 Views
0
AsNa_92
Helper I
Helper I

‎05-14-2024 10:13 PM

@vanessafvg 

Any luck on this? I have provided a sample data.

Message 6 of 11
180 Views
0
vanessafvg
Super User
Super User

‎05-12-2024 10:42 PM

please provide some sample data with your expected solution.

 

also how is your model setup?  do you have a separate date table, do you you have proper dates?  the quality of the solution will be based on the information provided.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Message 2 of 11
223 Views
AsNa_92
Helper I
Helper I
In response to vanessafvg

‎05-12-2024 10:58 PM

Hi @vanessafvg 

I don't have date table and if I created one it would be more difficult since I have other pages and tables that has mapping with a master table.

The line chart has a filter of (TYPE=QR) and (Attribute=MASS and GROSS RATE)

please find below a sample data:

TYPEDATEAttributeValue
QR12/31/2022 0:00MASS3.645585
QR12/31/2022 0:00GROSS RATE3.55798
QR12/31/2022 0:00NET RATE-0.08761
QR1/31/2023 0:00MASS3.910575
QR1/31/2023 0:00GROSS RATE3.731553
QR1/31/2023 0:00NET RATE-0.17902
QR2/28/2023 0:00MASS4.134447
QR2/28/2023 0:00GROSS RATE3.935703
QR2/28/2023 0:00NET RATE-0.19874
QR3/31/2023 0:00MASS4.312843
QR3/31/2023 0:00GROSS RATE4.272507
QR3/31/2023 0:00NET RATE-0.04034
QR4/30/2023 0:00MASS4.570048
QR4/30/2023 0:00GROSS RATE4.472209
QR4/30/2023 0:00NET RATE-0.09784
QR5/31/2023 0:00MASS4.958814
QR5/31/2023 0:00GROSS RATE4.68387
QR5/31/2023 0:00NET RATE-0.27494
QR6/30/2023 0:00MASS5.069737
QR6/30/2023 0:00GROSS RATE4.805832
QR6/30/2023 0:00NET RATE-0.2639
QR7/31/2023 0:00MASS5.190224
QR7/31/2023 0:00GROSS RATE4.952592
QR7/31/2023 0:00NET RATE-0.23763
QR8/31/2023 0:00MASS5.328825
QR8/31/2023 0:00GROSS RATE5.155238
QR8/31/2023 0:00NET RATE-0.17359
QR9/30/2023 0:00MASS5.336291
QR9/30/2023 0:00GROSS RATE5.155951
QR9/30/2023 0:00NET RATE-0.18034
QR10/31/2023 0:00MASS5.359936
QR10/31/2023 0:00GROSS RATE5.37812
QR10/31/2023 0:00NET RATE0.018184
QR11/30/2023 0:00MASS5.451271
QR11/30/2023 0:00GROSS RATE5.334495
QR11/30/2023 0:00NET RATE-0.11678
QR12/31/2023 0:00MASS5.350216
QR12/31/2023 0:00GROSS RATE5.289587
QR12/31/2023 0:00NET RATE-0.06063
QR1/31/2024 0:00MASS5.276858
QR1/31/2024 0:00GROSS RATE5.235922
QR1/31/2024 0:00NET RATE-0.04094
QR2/29/2024 0:00MASS5.241739
QR2/29/2024 0:00GROSS RATE5.140349
QR2/29/2024 0:00NET RATE-0.10139
BR12/31/2022 0:00MASS3.669163
BR12/31/2022 0:00GROSS RATE4.025409
BR12/31/2022 0:00NET RATE0.356246
BR1/31/2023 0:00MASS3.94377
BR1/31/2023 0:00GROSS RATE4.232851
BR1/31/2023 0:00NET RATE0.289081
BR2/28/2023 0:00MASS4.162243
BR2/28/2023 0:00GROSS RATE4.2254
BR2/28/2023 0:00NET RATE0.063157
BR3/31/2023 0:00MASS4.33412
BR3/31/2023 0:00GROSS RATE4.437535
BR3/31/2023 0:00NET RATE0.103415
BR4/30/2023 0:00MASS4.600813
BR4/30/2023 0:00GROSS RATE4.481358
BR4/30/2023 0:00NET RATE-0.11946
BR5/31/2023 0:00MASS4.960991
BR5/31/2023 0:00GROSS RATE4.741326
BR5/31/2023 0:00NET RATE-0.21967
BR6/30/2023 0:00MASS5.084874
BR6/30/2023 0:00GROSS RATE4.655785
BR6/30/2023 0:00NET RATE-0.42909
BR7/31/2023 0:00MASS5.205353
BR7/31/2023 0:00GROSS RATE4.925531
BR7/31/2023 0:00NET RATE-0.27982
BR8/31/2023 0:00MASS5.337966
BR8/31/2023 0:00GROSS RATE5.169445
BR8/31/2023 0:00NET RATE-0.16852
BR9/30/2023 0:00MASS5.347643
BR9/30/2023 0:00GROSS RATE5.120201
BR9/30/2023 0:00NET RATE-0.22744
BR10/31/2023 0:00MASS5.380123
BR10/31/2023 0:00GROSS RATE5.232378
BR10/31/2023 0:00NET RATE-0.14774
BR11/30/2023 0:00MASS5.477782
BR11/30/2023 0:00GROSS RATE5.054409
BR11/30/2023 0:00NET RATE-0.42337
BR12/31/2023 0:00MASS5.380563
BR12/31/2023 0:00GROSS RATE4.99344
BR12/31/2023 0:00NET RATE-0.38712
BR1/31/2024 0:00MASS5.287767
BR1/31/2024 0:00GROSS RATE4.987037
BR1/31/2024 0:00NET RATE-0.30073
BR2/29/2024 0:00MASS5.244648
BR2/29/2024 0:00GROSS RATE4.871114
BR2/29/2024 0:00NET RATE-0.37353
Total12/31/2022 0:00MASS3.656715
Total12/31/2022 0:00GROSS RATE3.778621
Total12/31/2022 0:00NET RATE0.121906
Total1/31/2023 0:00MASS3.925667
Total1/31/2023 0:00GROSS RATE3.959465
Total1/31/2023 0:00NET RATE0.033797
Total2/28/2023 0:00MASS4.146172
Total2/28/2023 0:00GROSS RATE4.057905
Total2/28/2023 0:00NET RATE-0.08827
Total3/31/2023 0:00MASS4.321593
Total3/31/2023 0:00GROSS RATE4.340376
Total3/31/2023 0:00NET RATE0.018782
Total4/30/2023 0:00MASS4.582402
Total4/30/2023 0:00GROSS RATE4.475882
Total4/30/2023 0:00NET RATE-0.10652
Total5/31/2023 0:00MASS4.959656
Total5/31/2023 0:00GROSS RATE4.706083
Total5/31/2023 0:00NET RATE-0.25357
Total6/30/2023 0:00MASS5.075227
Total6/30/2023 0:00GROSS RATE4.751415
Total6/30/2023 0:00NET RATE-0.32381
Total7/31/2023 0:00MASS5.195261
Total7/31/2023 0:00GROSS RATE4.943583
Total7/31/2023 0:00NET RATE-0.25168
Total8/31/2023 0:00MASS5.331771
Total8/31/2023 0:00GROSS RATE5.159817
Total8/31/2023 0:00NET RATE-0.17195
Total9/30/2023 0:00MASS5.339834
Total9/30/2023 0:00GROSS RATE5.144794
Total9/30/2023 0:00NET RATE-0.19504
Total10/31/2023 0:00MASS5.365898
Total10/31/2023 0:00GROSS RATE5.335075
Total10/31/2023 0:00NET RATE-0.03082
Total11/30/2023 0:00MASS5.459033
Total11/30/2023 0:00GROSS RATE5.252494
Total11/30/2023 0:00NET RATE-0.20654
Total12/31/2023 0:00MASS5.359022
Total12/31/2023 0:00GROSS RATE5.203653
Total12/31/2023 0:00NET RATE-0.15537
Total1/31/2024 0:00MASS5.279937
Total1/31/2024 0:00GROSS RATE5.165688
Total1/31/2024 0:00NET RATE-0.11425
Total2/29/2024 0:00MASS5.242518
Total2/29/2024 0:00GROSS RATE5.068287
Total2/29/2024 0:00NET RATE-0.17423
Message 3 of 11
217 Views
0
Sergii24
Super User
Super User
In response to AsNa_92

‎05-15-2024 02:29 AM

Hi @AsNa_92, I believe the problem statement is still unclear... What 2 lines are representing? What do you want to happen when you select Feb2024?

Message 4 of 11
150 Views
0
AsNa_92
Helper I
Helper I
In response to Sergii24

‎05-15-2024 04:28 AM

Hi @Sergii24 

the 2 lines are the Attribute and I want to show their values based on the selected date as end date and the start date should be one year age.

Message 5 of 11
145 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

View All