cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Line graph 6 months backward based on Dropdown selection

Hi Team,

I have created 2 line graphs, which is like last 6 months and 6 weeks view and also a dropdown for interaction with this line graph, see below

what i want is when i select the dropdown let say End of the month dropdown , lets say august 31st, it turns the graph like below

ideally what i want is when i select August 31st from the dropdown, i need the line grpah to show me August 31st and 6 months backward trend?

here is my pbix, appreciate any help

2 ACCEPTED SOLUTIONS
Super User

Hi,

You may refer to my solution in the attached PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

pls ignore i manage to find a solution

i created another measure

Inbound =
VAR __END_DATE_WEEK =
MAXX(ALLSELECTED('Dates(Disconnected)W'),'Dates(Disconnected)W'[End of Week])
VAR __END_DATE_MONTH =
MAXX(ALLSELECTED('Dates(Disconnected)M'),'Dates(Disconnected)M'[End of Month])
RETURN
IF(ISFILTERED('Dates(Disconnected)W'[End of Week]),
CALCULATE(
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),
FILTER(
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Week] = __END_DATE_WEEK
)
),
IF(ISFILTERED('Dates(Disconnected)M'[End of Month]),
CALCULATE(
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),
FILTER(
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Month] = __END_DATE_MONTH
)
),
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID])
)
)
11 REPLIES 11
Helper II

@danextian  thanks, could you check how i can interact this dropdowns to my Cards and graph, its available, in the Pbix, i think we need to apply some creative DAX

i am not abe to find a work around, given that we have now disconnected tables for End of week and End of month

Super User

Hi,

You may refer to my solution in the attached PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

You need to create disconnected dates table to reference in your measure. If you don't, the visual will show the dates within the range selected from the slicer. The disconnected dates table doesn't have a relationship to the fact table and can be created either in DAX,  M or enter data.  You will then need to create a measure that references the date selected from the disconnected table.
Sample measure:

``````Inbound2 =
VAR __END_DATE =
MAX ( 'Dates(Disconnected)'[End of Month] )
VAR __START_DATE =
EDATE ( __END_DATE, - 6 )
RETURN
CALCULATE (
DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),
FILTER (
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Month] >= __START_DATE
&& RECEIPT_CONTAINER[End of Month] <= __END_DATE
)
)

``````

Please refer to the attached pbix.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper II

its so cool, can you help me with the End of Week dropdown as well. thank so much, you are a savior

looks like its broken the dropdown

Helper II

@danextian  i replicated your formula for End of week

Inbound Week =
VAR __END_DATE =
MAX ( 'Dates(Disconnected)W'[End of Week])
VAR __START_DATE =
EDATE ( __END_DATE, - 6 )
RETURN
CALCULATE (
DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),
FILTER (
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Week] >= __START_DATE
&& RECEIPT_CONTAINER[End of Week] <= __END_DATE
)
)

the problem is it doesn't restrict the view to last 6 weeks, rather it shows up everything

Super User

EDATE function is for adding to or subracting x months from a date. The variable below is equivalent to selected date - 6 months.

``````VAR __START_DATE =
EDATE ( __END_DATE, - 6 )``````

``````VAR __START_DATE =
__END_DATE - ( 6  * 7 )``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper II

@danextian  thanks for the response

it doesn't work still, see below, i have used this dax

Inbound Week =
VAR __END_DATE =
MAX ( 'Dates(Disconnected)W'[End of Week])
VAR __START_DATE =
EDATE ( __END_DATE, - 6*7 )
RETURN
CALCULATE (
DISTINCTCOUNT ( RECEIPT_CONTAINER[CONTAINER_ID] ),
FILTER (
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Week] >= __START_DATE
&& RECEIPT_CONTAINER[End of Week] <= __END_DATE
)
)
could you please take a look at it, when you get sometime, there is another problem to the measure we are using, i think it doesn't interal with my graphs and cards, pls check

Super User

This was my suggestion as the variable:

``````VAR __START_DATE =
__END_DATE - ( 6  * 7 )``````

but you wrote:

``````VAR __START_DATE =
EDATE ( __END_DATE, - 6*7 )``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Helper II

@danextian  i think this formulas are not interacting with my cards and other visuals? do i need to make some tweak, i am aware the slicer you have used is coming from disconnected table

earliers it was from the Receipt container tables and was working with all visuals, now given that we have brought in disconnected tables, i am not able to interact with other visuals, as its only interacting with Line graphs

any solution you could share?

Helper II

hi, are you able to support or share some idea to help?

Otherwise these disconnected tables will not help, it may only help for the line graph, not the visuals on my dashboard

here is my pbix file, i have been looking for solution since  last 2 days, any help will be highly appreciated

Helper II

pls ignore i manage to find a solution

i created another measure

Inbound =
VAR __END_DATE_WEEK =
MAXX(ALLSELECTED('Dates(Disconnected)W'),'Dates(Disconnected)W'[End of Week])
VAR __END_DATE_MONTH =
MAXX(ALLSELECTED('Dates(Disconnected)M'),'Dates(Disconnected)M'[End of Month])
RETURN
IF(ISFILTERED('Dates(Disconnected)W'[End of Week]),
CALCULATE(
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),
FILTER(
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Week] = __END_DATE_WEEK
)
),
IF(ISFILTERED('Dates(Disconnected)M'[End of Month]),
CALCULATE(
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID]),
FILTER(
RECEIPT_CONTAINER,
RECEIPT_CONTAINER[End of Month] = __END_DATE_MONTH
)
),
DISTINCTCOUNT(RECEIPT_CONTAINER[CONTAINER_ID])
)
)

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors