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

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

Reply
vinodDrinkPak
Helper II
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

vinodDrinkPak_0-1694068740688.png

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

 

 

vinodDrinkPak_1-1694068817163.png

 

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

https://drive.google.com/file/d/1VKiy7yCzY7Aai3mHvctIHH5ITO4cOKQ6/view?usp=sharing

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@danextian 

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])
        )
    )

View solution in original post

11 REPLIES 11
vinodDrinkPak
Helper II
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

Ashish_Mathur
Super User
Super User

Hi,

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian
Super User
Super User

Hi @vinodDrinkPak ,

 

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.










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


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian 

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

vinodDrinkPak_0-1694071349081.png

 

@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
vinodDrinkPak_0-1694093819497.png

 

Hi @vinodDrinkPak 

 

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 )

Use this instead

VAR __START_DATE =
     __END_DATE - ( 6  * 7 )

 










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


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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

 

vinodDrinkPak_1-1694144195040.png

 

Hi @vinodDrinkPak ,

 

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 )









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


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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?

@danextian 

 

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

https://drive.google.com/file/d/1EP3BMNtm3ueuFrl0V9a_S5QB_tDXWas6/view?usp=sharing

 

@danextian 

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])
        )
    )

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.